Combining Tables
Because DML operations can be costly in terms of memory resources if the batch update mode is used, an alternative way to apply data updates is to use the MODIFY...TO COMBINE statement. This process merges the updates buffered in memory, and at the same time provides a way for performing bulk DML operations on any form of a table.
Examples:
1. DELETE FROM tab WHERE x > y
can be implemented with these statements:
CREATE TABLE deletions AS
SELECT key FROM tab WHERE x > y;
MODIFY tab EXCEPT deletions TO COMBINE;
DROP TABLE deletions;
2. UPDATE tab SET x = x + 1 WHERE x > y
can be implemented with these statements:
CREATE TABLE updates AS
SELECT key, CAST(x + 1 AS int) AS x, y WHERE x > y;
MODIFY tab EXCEPT updates UNION updates TO COMBINE
DROP TABLE updates;
3. DELETE FROM tab
can be implemented with this statement:
MODIFY tab EXCEPT tab TO COMBINE
Notes:
• Table "tab" has a primary key on the "key" column.
• The MODIFY...TO COMBINE statement works only if the column types and names of the new table match those of the original table. The example assumes that column x is type INT. When creating the updates table, the expression is casted so that it matches the type of the column in the original table. The expression "x+1" is remapped to the original column name ("AS x").
You can use similar solutions for other DML operations.
Last modified date: 01/30/2023