Hi,
The statement deletes the number of $DELETE_INCR rows from the table "table name" where RPT_DT = 'date', and counts the number of deleted rows that was actually deleted. The "old table" represents an intermediate result table containing the deleted rows.
Ref.
Retrieval of result sets from an SQL data change statementHere is an example to help you understand how the statement works.
1.Create a table.
$ db2 "create table t1(c1 int,c2 int)"
DB20000I The SQL command completed successfully.
2.Insert 5 records.
$ db2 "insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)"
DB20000I The SQL command completed successfully.
$ db2 "select * from t1"
C1 C2
----------- -----------
1 1
2 2
3 3
4 4
5 5
5 record(s) selected.
$ db2 "select count(1) from t1"
1
-----------
5
1 record(s) selected.
3.Select the Intermediate result table(deleted records) each time we delete 2 records from the table.
$ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))"
C1 C2
----------- -----------
1 1
2 2
2 record(s) selected.
$ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))"
C1 C2
----------- -----------
3 3
4 4
2 record(s) selected.
$ db2 "select * from old table (delete from (select * from t1 fetch first 2 rows only))"
C1 C2
----------- -----------
5 5
1 record(s) selected.
4.Populate 5 rows again.
$ db2 "insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5)"
DB20000I The SQL command completed successfully.
5.Now count the number of the Intermediate result table each time we delete 2 records from the table.
$ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))"
1
-----------
2
1 record(s) selected.
$ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))"
1
-----------
2
1 record(s) selected.
$ db2 "select count(1) from old table (delete from (select * from t1 fetch first 2 rows only))"
1
-----------
1
1 record(s) selected.
$ db2 "select * from t1"
C1 C2
----------- -----------
0 record(s) selected.
#Db2#Db2forLUW#Support#SupportMigration