Deleting rows with SQL Merge statement

Someone pointed out to me last week that the SQL Merge statement can delete rows from the table too. This struck me as bizarre as in my humble opinion a merge should just be the equivalent of insert and update. When I checked the documentation there it was, delete in the merge.

I have written about the update and insert of data into a table using the Merge in a previous post. I will be using the same files and tables in these examples, therefore, if you want to know more information about them please go to that article here.

I am going to start after the data from FILE1 has been inserted into TABLE1, using an Insert statement. I can view the contents of the table with this simple Select statement:

01 SELECT * FROM TABLE1

The contents of the table are:

KEY_COLUMN FIRST SECOND THIRD FOURTH FIFTH SIXTH ---------- ----- ---------- ------- ------ ----- ----- 1 AAAAA BBBBBBBBBB 0 .00 2 CCCCC DDDDDDDDDD 0 .00 6 EEEEE FFFFFFFFFF 0 .00

With my first merge statement I want to merge the data from FILE2 into the table. If I get a matched row and the column FIRST contains "AAAAA" delete the row from TABLE1. It may sound complicated but when you see the statement below it will be clearer what I am trying to achieve:

01 MERGE INTO TABLE1 A USING FILE2 B 02 ON A.KEY_COLUMN = B.F2KEY1 03 WHEN MATCHED AND A.FIRST = 'AAAAA' 04 THEN DELETE 05 WHEN MATCHED THEN 06 UPDATE SET A.THIRD = B.F2F1, 07 A.FOURTH = B.F2F2 08 WHEN NOT MATCHED THEN 09 INSERT (KEY_COLUMN,THIRD,FOURTH) 10 VALUES(B.F2KEY1,B.F2F1,B.F2F2)

Lines 1 and 2: Give the two files I am merging and the key field I am using to determine if this needs to be an insert or an update.

Lines 3 and 4: Here is where I am doing the delete. If there is a match with the key fields and row FIRST is equal to "AAAAA" then delete the row from TABLE1.

Lines 5 to 7: If the criteria on line 3 were not met then this is the next test that is performed. I this example this test will capture all matches where FIRST is not equal to "AAAAA".

Lines 8 – 10: All the unmatched rows in FILE2 and inserted into TABLE1.

When looking at the contents of TABLE1 using the following:

01 SELECT * FROM TABLE1

I see:

KEY_COLUMN FIRST SECOND THIRD FOURTH FIFTH SIXTH ---------- ----- ---------- ---------- ------ ----- ----- 2 CCCCC DDDDDDDDDD HHHHHHHHHH 2 .00 6 EEEEE FFFFFFFFFF 0 .00 3 IIIIIIIIII 3 .00 4 JJJJJJJJJJ 4 .00

Notice that the row where the column FIRST was equal to "AAAAA" is missing, as it was deleted.

In this example I want to merge in the data from FILE3, and also delete any existing rows in TABLE1 where the value in the column FOURTH is less than 1. That would be the row where KEY_COLUMN is "6".

01 MERGE INTO TABLE1 A USING FILE3 B 02 ON A.KEY_COLUMN = B.F3KEY1 03 WHEN MATCHED AND A.FOURTH < 1 04 THEN DELETE 05 WHEN MATCHED THEN 06 UPDATE SET A.FIFTH = B.F3F1, 07 A.SIXTH = B.F3F2 08 WHEN NOT MATCHED THEN 09 INSERT (KEY_COLUMN,FIFTH,SIXTH) 10 VALUES(B.F3KEY1,B.F3F1,B.F3F2)

The above statement is similar to the previous example.

Lines 3 and 4: If FILE3 and TABLE1 rows match and the value in column FOURTH is less than 1 then the row in TABLE1 is deleted.

Again I look in TABLE1:

01 SELECT * FROM TABLE1

I see:

KEY_COLUMN FIRST SECOND THIRD FOURTH FIFTH SIXTH ---------- ----- ---------- ---------- ------ 3 IIIIIIIIII 3 6.00 LLLLL 2 CCCCC DDDDDDDDDD HHHHHHHHHH 2 .00 6 EEEEE FFFFFFFFFF 0 .00 4 JJJJJJJJJJ 4 .00 1 0 5.00 KKKKK 5 0 7.00 MMMMM

The row where KEY_COLUMN is "6" is still present. Why?

That row would only have been deleted if there were matching records in both FILE3 and TABLE1. There is no record in FILE3 with a key of "6", therefore, there was no match and no delete.

SELECT F3KEY1 AS "Key" FROM FILE3 Key --- 1 3 5

Let me move the delete from the matched to the not matched "section".

01 MERGE INTO TABLE1 A USING FILE3 B 02 ON A.KEY_COLUMN = B.F3KEY1 03 WHEN MATCHED THEN 04 UPDATE SET A.FIFTH = B.F3F1, 05 A.SIXTH = B.F3F2 06 WHEN NOT MATCHED AND A.FOURTH < 1 07 THEN DELETE 08 WHEN NOT MATCHED THEN 09 INSERT (KEY_COLUMN,FIFTH,SIXTH) 10 VALUES(B.F3KEY1,B.F3F1,B.F3F2)

As I was executing this statement with the Run SQL Statements command, RUNSQLSTM, it generated a spool file with this error in it:

MSG ID SEV RECORD TEXT SQL0104 30 7 Position 8 Token DELETE was not valid. Valid tokens: SIGNAL.

I can understand why this happened. If I wanted to delete a row from TABLE1 that was not related to FILE3 surely it makes most sense to use a SQL Delete statement.

 

You can learn more about the SQL MERGE statement from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.