Database Reference
In-Depth Information
the syntax of the MERGE command, including updates for Oracle Data-
base 10
g
.
As you can see, there are two tables used in a MERGE command: (1)
the target table, which receives the inserts and updates, and (2) the source
table, which is used to determine whether to insert or update the target
table. Usually, the source table provides the data to be inserted or updated
in the target table, but you can also provide literal values, expressions, and
so on.
15.6.1
How To Use MERGE
Use the MERGE command when you need to handle ongoing inserts and
updates into a table. For an easy example of the MERGE command, first
create a new table that is an exact copy of the STUDIOTIME table, but
contains only nine of the rows. This simulates a situation where a copy of
the STUDIOTIME table was created at the end of the year 2000.
CREATE TABLE HISTORY_STUDIOTIME AS
SELECT * FROM STUDIOTIME
WHERE SESSION_DATE <= '31-DEC-00';
Now, let's imagine that it is the end of the year 2002 and you want to
add all the remaining rows into the HISTORY_STUDIOTIME table. In
addition, because payments were made on sessions from the year 2000,
imagine that some of the rows that already exist in the history table need to
be updated with some of the data from the current table. The MERGE
command would look as in the following script:
MERGE INTO HISTORY_STUDIOTIME HS
USING STUDIOTIME S ON (S.STUDIOTIME_ID = HS.STUDIOTIME_ID)
WHEN MATCHED THEN UPDATE
SET DUE_DATE = S.DUE_DATE,
AMOUNT_PAID = S.AMOUNT_PAID,
AMOUNT_CHARGED = S.AMOUNT_CHARGED
WHEN NOT MATCHED THEN INSERT VALUES
(S.STUDIOTIME_ID, S.ARTIST_ID, S.SESSION_DATE,
S.MINUTES_USED, S.DUE_DATE,
S.AMOUNT_CHARGED, S.AMOUNT_PAID);
SQL*Plus Worksheet will reply, “86 rows merged.”
