Selecting updated and deleted values when using SQL

Have you ever wanted to confirm that a SQL Update or Delete statement ran the way you expected?

In the new release, IBM i 7.6, I can do this using what IBM describes as:

1. Selecting updated values
2. Selecting deleted values

This was not added to the latest Technology Refresh for IBM i 7.5, TR6.

These are SQL Select statement that encapsulates the Update or Delete statement.

Before I get started with my examples, I need data I can use. I created a file called TESTTABLE that has these columns:

1. FIRST_NAME:  Person's first name
2. LAST_NAME:  Person's last name
3. STATE_CODE:  The US state or Canadian province they live in
4. COUNTRY_CODE:  The country they live in

The Table has four rows, that I can show with the following SQL Select statement:

01 SELECT * FROM TESTTABLE

Which returns:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON CA USA JOHN SMITH OH USA MARY GREENE BC CAN VICTORIA BOND DC USA

 

Selecting updated values

When I am updating data in a table or file there are three types of information, I could want to have:

1. What was the data before the update occurred
2. What is the data after the update occurred
3. The data before and after the update

The syntax for capturing this information is as follows:

01 SELECT < list of column names > 02 FROM OLD TABLE < or > FINAL TABLE 03 ( < Update statement > )

Line 1: All Select statements either need a list of columns or the asterisk ( * ) to denote all columns.

Line 2: With the Update I can either use:

• OLD TABLE the data is shown as it is before the update has been made
• FINAL TABLE the data is shown as it is after the update

Line 3: The SQL Update statement is encapsulated with parentheses ( ( ) ).

In the first example I need to change the state code for myself, Simon Hutchinson, as I no longer live in California, "CA", I now live in Texas, "TX".

The statements looks like:

01 SELECT * 02 FROM OLD TABLE (UPDATE TESTTABLE 03 SET STATE_CODE = 'TX' 04 WHERE FIRST_NAME = 'SIMON' 05 AND LAST_NAME = 'HUTCHINSON')

Line 1: I want to show all the columns from the Table.

Line 2: OLD TABLE means that I want to see the data as it was before the update was executed.

Line 3: Change the state code column to "TX".

Lines 4 and 5: For my record only.

When executed the result is:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON CA USA

To check if the update was successful, I need to run a Select statement over TESTTABLE, the same one as I did before:

01 SELECT * FROM TESTTABLE

Which shows that in my row the state code is now "TX":

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON TX USA JOHN SMITH OH USA MARY GREENE BC CAN VICTORIA BOND DC USA

I then find that Victoria Bond has moved from the District of Columbia, "DC", to Arizona, "AZ". I am going to use the FINAL TABLE as I want to see the data for her row after the update has been executed.

01 SELECT * 02 FROM FINAL TABLE (UPDATE TESTTABLE 03 SET STATE_CODE = 'AZ' 04 WHERE STATE_CODE = 'DC')

Line 2: The FINAL TABLE means that I want to see the results after the update has happened.

Line 3: Change the state code to "AZ".

Line 4: Here I am being lazy. Victoria is the only person in this Table who is in DC, rather than enter her name I am going to change all rows where the state code is DC to Arizona.

The result is:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- VICTORIA BOND AZ USA

I can check the TESTABLE, using the same statement I have used before, to confirm that the change has been made so that Victoria is now in Arizona:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON TX USA JOHN SMITH OH USA MARY GREENE BC CAN VICTORIA BOND AZ USA

The next example shows something I can only describe as really cool. I can add columns to the results, including the old values of the column I changed!

Mary Greene has moved from the Canadian province of British Columbia, "BC", to the province of Ontario, "ON". I am going to use the FINAL TABLE as I want to see the data after the Update has executed, but I also want to see the old value for the state code.

Here I would use the INCLUDE clause in the Update statement to define a new column. This can be used to define multiple new columns. My statement looks like this:

01 SELECT FIRST_NAME,LAST_NAME,STATE_CODE,COUNTRY_CODE, 02 OLD_STATE 03 FROM FINAL TABLE (UPDATE TESTTABLE 04 INCLUDE (OLD_STATE CHAR(2)) 05 SET STATE_CODE = 'ON', 06 OLD_STATE = STATE_CODE 07 WHERE STATE_CODE = 'BC' 08 AND COUNTRY_CODE = 'CAN')

Lines 1 and 2: As I have an additional column in the results, I need to give all the column names I want to be returned in the results. The new column is OLD_STATE.

Line 4: This is where I define the new column, OLD_STATE, as character 2 long.

Line 5: "ON" is going to be the new value in the column STATE_CODE.

Line 6: I am moving the contents of STATE_CODE to OLD_STATE, this happens before the update is executed, therefore, it will contain "BC".

Lines 7 and 8: I am changing all the rows where the state code is British Columbia and the country code is Canada.

The results are:

FIRST_ LAST_ STATE COUNTRY OLD_ NAME NAME _CODE _CODE STATE -------- ---------- ----- ------- ----- MARY GREENE ON CAN BC

I see the new value in the state code, "BC", and the old value in the old state code.

Checking the TESTTABLE shows that Mary's row was changed:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON TX USA JOHN SMITH OH USA MARY GREENE ON CAN VICTORIA BOND AZ USA

 

Selecting deleted values

When I delete a row I cannot use the FINAL TABLE as the row will not exist. Therefore, with a delete I can only use the OLD_TABLE. This means that the syntax for this is:

01 SELECT < list of column names > 02 FROM OLD TABLE 03 ( < Delete statement > )

For my first example of a delete I am going to delete John Smith's row from TESTTABLE:

01 SELECT * 02 FROM OLD TABLE (DELETE FROM TESTTABLE 03 WHERE FIRST_NAME = 'JOHN' 04 AND LAST_NAME = 'SMITH')

The "outside" part of this statement, the Select, is the same as it was for the update. The "inside" part, the Delete, deletes all the rows where the name is "John Smith".

The result is as follows:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- JOHN SMITH OH USA

And John's row has been deleted from TESTTABLE:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- SIMON HUTCHINSON TX USA MARY GREENE ON CAN VICTORIA BOND AZ USA

In the next example I am going to delete the remaining USA rows, and I want a column to show me when the delete happened:

01 SELECT FIRST_NAME,LAST_NAME,STATE_CODE,COUNTRY_CODE, 02 WHEN_DELETED 03 FROM OLD TABLE (DELETE FROM TESTTABLE 04 INCLUDE(WHEN_DELETED TIMESTAMP) 05 SET WHEN_DELETED = CURRENT_TIMESTAMP 06 WHERE COUNTRY_CODE = 'USA')

Lines 1 and 2: I need to list all the columns, including the new one WHEN_DELETED.

Line 4: WHEN_DELETED is defined as a timestamp in the INCLUDE clause.

Line 5: WHEN_DELETED is set to contain the current timestamp when the delete happened.

Line 6: I am deleting all the USA rows.

The result is:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE WHEN_DELETED -------- ---------- ----- ------- -------------------------- SIMON HUTCHINSON TX USA 2025-05-11 18:19:07.929712 VICTORIA BOND AZ USA 2025-05-11 18:19:07.929712

And TESTTABLE only contains Mary Green's row as she is in Canada:

FIRST_ LAST_ STATE COUNTRY NAME NAME _CODE _CODE -------- ---------- ----- ------- MARY GREENE ON CAN

 

All of the above examples are fine as examples. In a more "real world" scenario I am more likely to do this in a RPG program, that using ACS's RSS. The RPG program below will change all the rows for one state to another, as there could be more than one row changed, I need to use an SQL cursor:

01 **free 02 dcl-ds Data extname('TESTTABLE') qualified dim(*auto : 9999) alias ; 03 OLD_STATE char(2) ; 04 WHEN_CHANGED timestamp ; 05 end-ds ; 06 dcl-s Elements uns(5) inz(%elem(Data : *max)) ; 07 dcl-s FromState char(2) ; 08 dcl-s ToState like(FromState) ; 09 FromState = 'CA' ; 10 ToState = 'TX' ; 11 exec sql DECLARE C0 CURSOR FOR 12 SELECT FIRST_NAME,LAST_NAME,STATE_CODE,COUNTRY_CODE, 13 OLD_STATE,WHEN_CHANGED 14 FROM FINAL TABLE (UPDATE TESTTABLE 15 INCLUDE(OLD_STATE CHAR(2), 16 WHEN_CHANGED TIMESTAMP) 17 SET STATE_CODE = :ToState, 18 OLD_STATE = STATE_CODE, 19 WHEN_CHANGED = CURRENT_TIMESTAMP 20 WHERE STATE_CODE = :FromState) ; 21 exec sql OPEN C0 ; 22 exec sql FETCH C0 FOR :Elements ROWS INTO :Data ; 23 exec sql CLOSE C0 ; 24 *inlr = *on ;

Lines 2 – 5: The results from the Select Update statements will be placed into this Data Structure array, called Data.

Line 2: The EXTNAME keyword states that the data structure will be defined to be the same as the columns in the Table TESTTABLE. It is an auto extending array of up to 9,999 elements. The ALIAS keyword allows me to use the long "SQL" names for the data structure's subfields.

Lines 3 and 4: These two subfields are added to the "bottom" of the data structure, after all the subfields defined from the Table TESTTABLE.

Line 6: I need to define a variable to contains the maximum elements the data structure array can have.

Line 7: Variable to contain the state code that is going to be changed.

Line 8: Variable to contain what the state code will be changed to.

Line 9: The state code to be changed is "CA".

Line 10: The state code will be change to "TX".

Lines 11 – 20: Cursor definition for my statement.

Line 12 – 13: I list all the columns from the Table as I have two additional columns, OLD_STATE and WHEN_CHANGED.

Line 14: I using the FINAL TABLE for this Update.

Lines 15 and 16: I have defined the two new columns, OLD_STATE and WHEN_CHANGED.

Line 17: Change the state code to the value in the RPG variable ToState.

Line 18: Move the value in the state code to OLD_STATE.

Line 19: Move the current timestamp to the WHEN_CHANGED.

Line 20: For all records where the state code is the same as the value in the FromState RPG variable.

Line 21: Open the cursor.

Line 22: Fetch up to the maximum number of array elements of the data structure, 9,999, into the Data array.

Line 23: Close the cursor.

After creating the program, I add the debug breakpoint at line 24. When I call the program, and check the contents of the data structure array Data I see the following:

> EVAL data DATA.FIRSTNAME(1) = 'SIMON ' DATA.LASTNAME(1) = 'HUTCHINSON ' DATA.STATE(1) = 'TX' DATA.COUNTRY(1) = 'USA' DATA.OLDSTATE(1) = 'CA' DATA.WHENCHANGED(1) = '2025-05-11-19.07.09.666236'

This shows that the state code was changed to "TX" from "CA" on May 11 at 7:07 PM

I think this is going to be a useful to use. I can already think of two or three places I could use this at my work.

 

You can learn more about this from the IBM website:

• Selecting updated values
• Selecting deleted values

 

This article was written, and will only work, for IBM i 7.6.