Oracle's
Merge Command
New in 9i was the merge command which has a syntax rather like this...
New in 9i was the merge command which has a syntax rather like this...
MERGE
INTO TargetTable T
USING SourceTable S
ON (S.Column1 = T.Column2
AND S.Column3 = T.Column4)
WHEN MATCHED
THEN UPDATE
SET T.Column5 = S.Column6,
T.Column7 = S.Column8
WHEN NOT MATCHED
THEN INSERT (T.Column9,
T.Column10)
VALUES (S.Column11,
S.Column12)
INTO TargetTable T
USING SourceTable S
ON (S.Column1 = T.Column2
AND S.Column3 = T.Column4)
WHEN MATCHED
THEN UPDATE
SET T.Column5 = S.Column6,
T.Column7 = S.Column8
WHEN NOT MATCHED
THEN INSERT (T.Column9,
T.Column10)
VALUES (S.Column11,
S.Column12)
This
is much nicer
than the previous options of either doing seperate updates and inserts
based on the presence or absence of the tested values or, even more
horrible, using a cursor loop to step through every row in the source
table and then
doing
conditional inserts or updates.
It's a really usefull addition to the SQL programmer's toolset and should be the first option considered when upserting is required.
It's a really usefull addition to the SQL programmer's toolset and should be the first option considered when upserting is required.
Find out more
by searching Google here...