There are certain situations, especially within Data Warehouse applications, where you may want to either insert a new row into a table or update an existing row depending on whether or not the data already exists in the table. For example, you may receive a nightly feed of parts data that contains both parts that are known to the system along with parts just introduced by your suppliers. If a part number exists in the part table, you will need to update the unit_cost and status columns; otherwise, you will need to insert a new row.

While you could write code that reads each record from the feed, determines whether or not the part number exists in the part table, and issues either an INSERT or UPDATE statement, you could instead issue a single MERGE statement.*Assuming that your data feed has been loaded into the part_stg staging table, your MERGE statement would look something like the following:

1  MERGE INTO part p_dest
2  USING part_stg p_src
3  ON (p_dest.part_nbr = p_src.part_nbr)
4  WHEN MATCHED THEN UPDATE
5    SET p_dest.unit_cost = p_src.unit_cost, 
  p_dest.status = p_src.status
6  WHEN NOT MATCHED THEN INSERT (p_dest.part_nbr, 
  p_dest.name,
7    p_dest.supplier_id, p_dest.status, 
  p_dest.inventory_qty,
8    p_dest.unit_cost, p_dest.resupply_date)
9    VALUES (p_src.part_nbr, p_src.name,
10   p_src.supplier_id, p_src.status, 0, p_src.unit_cost, 
   null);

This statement looks fairly complex, so here is a description of what it is doing:

Lines 1–3

For each row in the part_stg table, see if the part_nbr column exists in the part table.

Lines 4–5

If it does, then update the matching row in the part table using data from the part_stg table.

Lines 6–10

Otherwise, insert a new row into the part table using the data from the part_stg table.

So Why Are There 17 More Chapters?

After reading this chapter, you might think that SQL looks pretty simple (at least the DML portion). At a high level, it is fairly simple, and you now know enough about the language to go write some code. However, you will learn over time that there are numerous ways to arrive at the same end point, and some are more efficient and elegant than others. The true test of SQL mastery is when you no longer have the desire to return to what you were working on the previous year, rip out all the SQL, and recode it. For one of us, it took about nine years to reach that point. Hopefully, this book will help you reach that point in far less time.

While you are reading the rest of the book, you might notice that the majority of examples use SELECT statements, with the remainder somewhat evenly distributed across INSERT, UPDATE, and DELETE statements. This disparity is not indicative of the relative importance of SELECT statements over the other three DML statements; rather, SELECT statements are favored because we can show a query’s result set, which should help you to better understand the query, and because many of the points being made using SELECT statements can be applied to UPDATE and DELETE statements as well.

* Depending on the purpose of the model, entities may or may not
   correlate to database tables. For example, a logical model depicts
   business entities and their relationships, whereas a physical model
   illustrates tables and their primary/foreign keys. The model in Figure
   1-1 is a physical model.

* NULL indicates the absence of a value. The use of NULL is covered in
   Chapter 2.

* MERGE was introduced in Oracle9i.

This article is excerpted from Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN  1590593006). Check it out at your favorite bookstore today. Buy this book now.