problem with SQL using where exists
200396Dec 11 2008 — edited Dec 11 2008I am having a problem with an update I am trying using two database tables on Oracle 8 (I know this is old!).
One table has addresses with old zip codes, and I need to update zip codes from a second table. There is a location ID key on both, but there are multiple records with this key on the first table.
I have tried the following
update location_alarm a set (a.zip_code) = (select b.zip_code from location b) where exists (select 1 from location b, location_alarm a where a.location_id = b.location_id)
This fails with ORA 01427 single row subquery returns more than one row. THis is expected because of duplicated key in first table of course. Any ideas please about how I can do this. THis is 8i so later extensions in 9 & 10 won't apply.
Many thanks