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.
SET FIELD1 =
FROM SOURCE_TABLE DM
WHERE DM.FIELD1 = FIELD1
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.
FROM DEST_TABLE DES
SET FIELD2 = SRC.FIELD2
WHERE DES.FIELD1 = COALESCE(SRC.FIELD1, 'Unknown');