Can't get update to work using 2 tables

[jlindahl]

I am trying to use the following sql statement to update a table based on values from another table.

Update grid_master_stg gms set gms.grid_key = (select gm.grid_key from grid_master gm where gm.grid_lvl = gms.grid_lvl and gm.grid_type = gms.grid_type and gm.grid_name = gms.grid_name) where exists (select * from grid_master gm where gm.grid_lvl = gms.grid_lvl and gm.grid_type = gms.grid_type and gm.grid_name = gms.grid_name)

The error message I receive is: single row subquery returns more than one row.

I thought you could use "exists" with a subquery that returns multiple rows. I am using c# code and and Oracle 9 database.
Can anyone please help me get this working?

Thanks!!

 

[PHV]

The error don't come from the EXISTS subquery but the previous: You can't assign multiple values to grid_key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zathras]

You could try
[tt]
...set gms.grid_key = (select DISTINCT gm.grid_key from...
[/tt]

 

[jlindahl]

yeah, that looks like what is going on. now i have to figure out why there are duplicate in a table that is not supposed to be holding duplicates.

thanks for all the help!!