Merge help you to update and insert a new record with the same instruction.
the following present the syntax of it.
MERGE INTO Table1 T1
USING (SELECT Id, fields FROM Table2) T2
ON ( T1.Id = T2.Id ) -- Matching condition
WHEN MATCHED THEN -- true
UPDATE SET T1.fields = T2.fields --many fields with separator (,)
WHEN NOT MATCHED THEN -- false
INSERT (T1.ID, T1.fields) VALUES ( T2.ID, T2.fields);
Example
>SQLPLUS scott/tiger Oracle Database 10g Release 10.2.0.1.0 - Production SQL> SET LINESIZE 130 SQL> CREATE Table product ( 2 Id Number (10), 3 Ref VARCHAR2 (16), 4 Price NUMBER (12,2)); SQL> Table created SQL>CREATE SEQUENCE Seq_Id_Pro START WITH 1 INCREMENT BY 1; SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '001', 5.50); SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '002', 3.5); SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '004', 5.99); SQL>COMMIT; --the second table SQL> CREATE Table Temp_Product ( 2 Ref VARCHAR2 (16), 3 Price NUMBER (12,2)); SQL> Table created SQL>INSERT INTO Temp_Product VALUES ('001', 6.99); SQL>INSERT INTO Temp_Product VALUES ('002',2.5); SQL>INSERT INTO Temp_Product VALUES ('003', 2.99); SQL>INSERT INTO Temp_Product VALUES ('004', 5.5); SQL>INSERT INTO Temp_Product VALUES ('005', 4.9); SQL>COMMIT; SQL> MERGE INTO Product P 2 USING (SELECT Ref, price FROM Temp_Product) T 3 ON (P.Ref = T.Ref) 4 WHEN MATCHED THEN 5 UPDATE SET P.Price = T.Price -- you can other fields 6 WHEN NOT MATCHED THEN 7 INSERT (P.Id, P.Ref, P.Price) VALUES (Seq_Id_Pro.NextVal, T.Ref, T.Price); SQL>5 records matched. --when you do select on table product you will find update of the price and a new records inserted. ; SQL>select * from product; ID REF PRICE ---------- ---------------- ---------- 1 001 6,99 2 004 5,5 3 002 2,5 7 003 2,99 8 005 4,9 --know we have upadate and new records at the same times. |
