SQL as A Data Manipulation Language : MERGE

The MERGE statement is used to select rows from one or more sources for update or insertion into a table or view. We can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements. MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

For example suppose we are having two tables new_stock and old_stock with the following structure:

 old_stock table :
 SQL> SELECT * FROM old_stock;

 ITEM                QUANTITY
 -------------------- ----------
 Mother-Board          10 
 RAM                   10
 Processor              8
new_stock table :
 SQL> SELECT * FROM new_stock;
 
 ITEM                 QUANTITY
 -------------------- ----------
 Mother-Board          15
 RAM                   20
 Processor             10
 Monitor               20
 Keyboard              25
 Mouse                 25
 Cabinet               25
Now we want to update old_stock table from new_stock table i.e those rows which are already present in old_stock, their quantity should be updated and those rows which are not present in old_stock table should be inserted. Now the SQL query will be :
 MERGE INTO old_stock os 
 USING new_stock ns 
 ON ( ns.item=os.item )
 WHEN MATCHED THEN
          UPDATE SET os.quantity = (os.quantity+ns.quantity)
 WHEN NOT MATCHED THEN
          INSERT VALUES (ns.item, ns.quantity); 
After the statement is executed old_stock table will look like this.
 SQL> SELECT * FROM old_stock;

 ITEM                QUANTITY
 ------------------- ----------
 Mother-Board          25
 RAM                   30
 Processor             18
 Mouse                 25
 Cabinet               25
 Monitor               20
 Keyboard              25


Next Topic :

No comments:

Post a Comment