Oracle 10g includes a number of amendments to the
MERGE
statement making it more flexible.- Test Table
- Optional Clauses
- Conditional Operations
- DELETE Clause
Test Table
The following examples use the table defined below.
CREATE TABLE test1 AS SELECT * FROM all_objects WHERE 1=2;
Optional Clauses
The
MATCHED
and NOT MATCHED
clauses are now optional making all of the following examples valid.-- Both clauses present. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No matched clause, insert only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No not-matched clause, update only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status;
Conditional Operations
Conditional inserts and updates are now possible by using a
WHERE
clause on these statements.-- Both clauses present. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID' WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status) WHERE b.status != 'VALID'; -- No matched clause, insert only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status) WHERE b.status != 'VALID'; -- No not-matched clause, update only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID';
DELETE Clause
An optional
DELETE WHERE
clause can be added to the MATCHED
clause to clean up after a merge operation. Only those rows in the destination table that match both the ON
clause and the DELETE WHERE
are deleted. Depending on which table the DELETE WHERE
references, it can target the rows prior or post update. The following examples clarify this.
Create a source table with 5 rows as follows.
Create the destination table using a similar query, but this time with 10 rows.CREATE TABLE source AS SELECT level AS id, CASE WHEN MOD(level, 2) = 0 THEN 10 ELSE 20 END AS status, 'Description of level ' || level AS description FROM dual CONNECT BY level <= 5; SELECT * FROM source; ID STATUS DESCRIPTION ---------- ---------- ----------------------- 1 20 Description of level 1 2 10 Description of level 2 3 20 Description of level 3 4 10 Description of level 4 5 20 Description of level 5 5 rows selected. SQL>
CREATE TABLE destination AS SELECT level AS id, CASE WHEN MOD(level, 2) = 0 THEN 10 ELSE 20 END AS status, 'Description of level ' || level AS description FROM dual CONNECT BY level <= 10; SELECT * FROM destination; 1 20 Description of level 1 2 10 Description of level 2 3 20 Description of level 3 4 10 Description of level 4 5 20 Description of level 5 6 10 Description of level 6 7 20 Description of level 7 8 10 Description of level 8 9 20 Description of level 9 10 10 Description of level 10 10 rows selected. SQL>
The following
MERGE
statement will update all the rows in the destination table that have a matching row in the source table. The additional DELETE WHERE
clause will delete only those rows that were matched, already in the destination table, and meet the criteria of the DELETE WHERE
clause.MERGE INTO destination d USING source s ON (s.id = d.id) WHEN MATCHED THEN UPDATE SET d.description = 'Updated' DELETE WHERE d.status = 10; 5 rows merged. SQL> SELECT * FROM destination; ID STATUS DESCRIPTION ---------- ---------- ----------------------- 1 20 Updated 3 20 Updated 5 20 Updated 6 10 Description of level 6 7 20 Description of level 7 8 10 Description of level 8 9 20 Description of level 9 10 10 Description of level 10 8 rows selected. SQL>
Notice there are rows with a status of "10" that were not deleted. This is because there was no match between the source and destination for these rows, so the delete was not applicable.
The following example shows the
DELETE WHERE
can be made to match against values of the rows before the update operation, not after. In this case, all matching rows have their status changed to "10", but the DELETE WHERE
references the source data, so the status is checked against the source, not the updated values.ROLLBACK; MERGE INTO destination d USING source s ON (s.id = d.id) WHEN MATCHED THEN UPDATE SET d.description = 'Updated', d.status = 10 DELETE WHERE s.status = 10; 5 rows merged. SQL> SELECT * FROM destination; ID STATUS DESCRIPTION ---------- ---------- ----------------------- 1 10 Updated 3 10 Updated 5 10 Updated 6 10 Description of level 6 7 20 Description of level 7 8 10 Description of level 8 9 20 Description of level 9 10 10 Description of level 10 8 rows selected. SQL>
Notice, no extra rows were deleted compared to the previous example.
By switching the DELETE WHERE to reference the destination table, the extra updated rows can be deleted also.
ROLLBACK; MERGE INTO destination d USING source s ON (s.id = d.id) WHEN MATCHED THEN UPDATE SET d.description = 'Updated', d.status = 10 DELETE WHERE d.status = 10; 5 rows merged. SQL> SELECT * FROM destination; ID STATUS DESCRIPTION ---------- ---------- ----------------------- 6 10 Description of level 6 7 20 Description of level 7 8 10 Description of level 8 9 20 Description of level 9 10 10 Description of level 10 5 rows selected. SQL>
0 comments:
Post a Comment