There have been times, many times, when I have needed to update the one or more columns in a table with values retrieved from another. My usual approach to this is to use an
UPDATE with subselects but it turns out that there is a better way:
The MERGE statement updates a target (a table or view) using data from a source (result of a table reference). Rows in the target that match the input data may be updated or deleted as specified, and rows that do not exist in the target may be inserted as specified. Updating, deleting, or inserting a row in a view updates, deletes, or inserts the row into the tables on which the view is based if no INSTEAD OF trigger is defined on the view.
In order to play around with this, I build and populated a couple of test tables (imaginatively named
TEST02) using the below scripts:
create table test01 (key_field char(1) not null, data01 char(20), data02 char(30), data03 char(5), primary key (key_field)); insert into test01 (Key_field, data01) values('A', 'TEXT One'); insert into test01 (Key_field, data01) values('B', 'TEXT Two'); insert into test01 (Key_field, data01) values('D', 'TEXT Four');
create table test02 (key_field char(1) not null, data01 char(20), data02 char(30), data03 char(5), primary key (key_field)); insert into test02 (Key_field, data02) values('A', 'More text one'); insert into test02 (Key_field, data02, data03) values('C', 'more text two', 'CODE'); insert into test02 (Key_field, data01, data02) values('E', 'TEXT five', 'More text five');
This gives me two tables with one matched row (
key_field=’A’). To update the first table with the values in the second is really rather simple:
MERGE INTO test01 a USING (SELECT key_field, data01, data02, data03 from test02) b ON b.key_field = a.key_field WHEN MATCHED THEN UPDATE SET data02 = b.data02, data03 = b.data03;
And the one matched row is updated in table
TEST01 with the data from table
If this was all I could do, I would be happy to have replaced a rather clunky approach with a more readable solution. But now the
MERGE statement really starts to make a difference:
MERGE INTO test01 a USING (SELECT key_field, data01, data02, data03 from test02) b ON b.key_field = a.key_field WHEN MATCHED THEN UPDATE SET data02 = b.data02, data03 = b.data03 WHEN NOT MATCHED THEN INSERT (key_field, data01, data02, data03) VALUES (b.key_field, b.data01, b.data02, b.data03) NOT ATOMIC CONTINUE ON SQLEXCEPTION;
WHEN NOT MATCHED clause means that not only can I update the matched rows, I can also do something about the rows that exist in table
TEST02 that have now matched record in table
TEST01. In this example, I have inserted the rows from table
TEST02, other options allow me to either delete the row from table
TEST01 or raise a SIGNAL statement.
NOT ATOMIC CONTINUE ON SQLEXCEPTION specifies that if an error is encountered on the
MERGE will continue. Bad data happens, this saves be the burden of constantly having to refine my selection criteria to avoid the crap.
And a final note on the subject of selection criteria, the
WHEN MATCHED and
WHEN NOT MATCHED can both be extended with an
AND to refine the selection to exactly what you want.
Do go and read up on the
MERGE statement. It provides a powerful and flexible approach to updating tables from source data and one that I will be using in the very near future.