Difference between revisions of "Oracle/Merge"
Jump to navigation
Jump to search
m |
|||
| Line 7: | Line 7: | ||
ON (cn.id = c.id) | ON (cn.id = c.id) | ||
WHEN MATCHED THEN -- update existing | WHEN MATCHED THEN -- update existing | ||
UPDATE SET c.name = cn.name, e.address_id = cn.address_id | UPDATE SET c.name = cn.name, e.address_id = cn.address_id | ||
WHEN NOT MATCHED THEN -- create missing (new) | WHEN NOT MATCHED THEN -- create missing (new) | ||
INSERT INTO (cn.id, cn.name, cn.address_id) | INSERT INTO (cn.id, cn.name, cn.address_id) | ||
Revision as of 10:15, 7 December 2009
The Oracle MERGE statement may be used for various purposes. Its main function is to merge data from one table (or view) into another table. Inserts and Updates can be performed, Deletes are available since Oracle 10g.
Merge new and updated client data from table client_new into table client:
MERGE INTO clients c
USING (SELECT id, name, address_id FROM clients_new) cn
ON (cn.id = c.id)
WHEN MATCHED THEN -- update existing
UPDATE SET c.name = cn.name, e.address_id = cn.address_id
WHEN NOT MATCHED THEN -- create missing (new)
INSERT INTO (cn.id, cn.name, cn.address_id)
VALUES (c.id, c.name, c.address_id); -- we assume that there will be no id conflicts!
An example for Update using join and merge may be found here.