Update a table using a Subquery from another table

Database
Enthusiast

Update a table using a Subquery from another table

I am trying to update a table and I have several fields in my set operation.  In Oracle this same query works with the same data/table structure to return 1 result and update the column.  The source table only have maybe 10 rows as it is a lookup table and they are all unique.

Here is what I am trying to do.

SQL Statement


UPDATE DEST_TABLE


SET FIELD1 =


COALESCE((


SELECT DM.FIELD2


FROM SOURCE_TABLE DM


WHERE DM.FIELD1 = FIELD1

),'Unknown');

Error:

Executed as Single statement.  Failed [3669 : HY000] More than one value was returned by a subquery.

Elapsed time = 00:00:00.734

STATEMENT 1: Update Statement failed.

I was able to get this work using this query instead, but I will like to find out why I can't use the subquery above.


UPDATE DES


FROM DEST_TABLE DES


,SOURCE_TABLE SRC


SET FIELD2 = SRC.FIELD2


WHERE DES.FIELD1 = COALESCE(SRC.FIELD1, 'Unknown');

1 REPLY
Enthusiast

Re: Update a table using a Subquery from another table

Any ideas?