Wednesday, January 26, 2022

Deleting rows with SQL Merge statement

sql merge delete

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.

4 comments:

  1. Simon, great information. Thanks for sharing.

    ReplyDelete
  2. Great thanks for the sharing

    ReplyDelete
  3. Oh no! Matching records. What's next. look ahead fields. Back in 98 I had to maintain a program that used the cycle, primary file, secondary file, matching records, a look ahead field, level breaks at caclc time and total time. Not fun.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.