Depending on your requirements, MERGE might be better than UPDATE.
UPDATE table1 t1 SET flag_column = 1 WHERE EXISTS ( SELECT 1 FROM table2 WHERE columnX = t1.columnX AND columnY = t1.columnY ) ;
977424 wrote:DISTINCT isn't necessary; EXISTS sub-queries never raise the "... returns too many rows" error. In fact, EXISTS sub-queries stop as soon as they find a row. However, DISTINCT isn't doing any harm in this case.
This statement works, thank you. The only thing I did was add a distinct in order to get rid of the "single-row sub query..." error
Hence, my real query is:MERGE is a big improvement over UPDATE when the UPDATE statement has a sub-query in the SET clause, and then has a similar sub-query in the WHERE clause. Also, use MERGE instead of UPDATE when you need analytic functions.
UPDATE mn003p t1
SET DIM_PROCESS_FLAG = 0
WHERE EXISTS (
SELECT distinct 1
WHERE item_number = t1.item_number
AND product_identifier = t1.product_identifier
This works great.
In what case would I use the MERGE statement? Do you know which is faster (MERGE vs UPDATE)?