MERGE as a better UPDATE

If you want to UPDATE a table using a join you will encounter a set of relentless errormessages. The lesser known MERGE-Statement might be just what you need.

UPDATE will not work for something like this:

UPDATE table1 JOIN table2 ON table1.id = table2.id 
SET table1.value = table2.value;

With MERGE it will work:

MERGE INTO table1 USING
table1 JOIN table2 ON table1.id = table2.id
WHEN matched THEN
UPDATE SET table1.value = table2.value;

There is even more to the MERGE-Statement, like a clause for not-matching-items or using SELECT-Statements as source. I am using an Oracle Database but there are similar statements on other systems, too.