|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
July 12th, 2006, 02:29 PM
|
|||
|
|||
|
SQL Error: ORA-01427: single-row subquery returns more than one row
I am trying to update the parcel_id column in burn_permits.burn with info from the pan column in the gis_base.parcel_fairbanks. This is basically a one time update but I keep getting the above error message. My current update statement is below. Any help would be appreceiated.
UPDATE burn_permits.burn a SET (a.parcel_id) = (SELECT pan FROM gis_base.parcel_fairbanks b WHERE trim (' ' from upper(b.SITUS_STRE ||' '|| b.SITUS__NUM)) = trim(' ' from upper(a.address))) WHERE EXISTS (SELECT 'x' FROM gis_base.parcel_fairbanks b WHERE trim (' ' from upper(b.SITUS_STRE ||' '|| b.SITUS__NUM)) = trim(' ' from upper(a.address))); I posted this in the coldfusion forums as well due to the fact my pages are designed with CF. 
|
|
#2
July 12th, 2006, 03:31 PM
|
|||
|
|||
|
I made a guess about your data - the error is from the SELECT pan... statement
Code:
UPDATE burn_permits.burn a
SET (a.parcel_id) = (SELECT pan
FROM gis_base.parcel_fairbanks b
WHERE ROWNUM=1 AND
trim (' ' from upper(b.SITUS_STRE ||' '|| b.SITUS__NUM)) = trim(' ' from upper(a.address)))
WHERE
EXISTS (SELECT 'x'
FROM gis_base.parcel_fairbanks b
WHERE trim (' ' from upper(b.SITUS_STRE ||' '|| b.SITUS__NUM)) = trim(' ' from upper(a.address)));
|
|
#4
July 13th, 2006, 03:44 AM
|
||||
|
||||
|
Quote:
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias)
|
|
| Viewing: Dev Shed Forums > Databases > Oracle Development > SQL Error: ORA-01427: single-row subquery returns more than one row |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
