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
insertin the followingMergingTable_Targettable if the ID fromMergingTable_Sourcedoes not exist. And if it does, we will update theEND_DTandSOLD_AMTcolumns:
MERGE INTO MergingTable_Target t USING MergingTable_Source s...