MERGE
statements can be used as an alternative to update statements. Updating rows is a CPU and I/O intensive operation, and, if you are updating PI on a big table which does not have a partition column, then updating columns becomes a challenge.
CREATE volatile TABLE MergingTable_Source ( ID DECIMAL(18,0), END_DT DATE, SOLD_AMT int ) UNIQUE PRIMARY INDEX (ID) on commit preserve rows;
- Create a target table with the following DDL:
CREATE volatile TABLE MergingTable_Target ( ID DECIMAL(18,0), END_DT DATE, SOLD_AMT int ) UNIQUE PRIMARY INDEX (ID);
- We will
insert
in the followingMergingTable_Target
table if the ID fromMergingTable_Source
does not exist. And if it does, we will update theEND_DT
andSOLD_AMT
columns:
MERGE INTO MergingTable_Target t USING MergingTable_Source s...