0

How can I insert the rows deleted in a delete statement into a new table within a stored procedure in DB2 SQL?

DB2 allows the following syntax to return the deleted rows:

select * from old table (
    delete from my_table where foo > 1
)

For some reason, you can't just do an insert based on the results returned from that statement. However, you can use common table expressions as a kludgy workaround:

with deleted as (
    select * from old table (delete from my_table where foo > 1)
)
select * from new table (insert into archive_table select * from deleted)

This has an unnecessary extra select statement that I don't want, but at least it works. Deleted rows get inserted into another table.

However, how can I do this within a stored procedure?

A stored procedure doesn't allow a bare select statement. I thought of putting it within a set statement:

set my_var = (
    with deleted as (
        select * from old table (delete from my_table where foo > 1)
    )
    select * from new table (insert into archive_table select * from deleted)
);

However, this fails because a common table expression is not allowed within such a statement.

Is there any way to do this within a stored procedure?

(The task can be done using some other method as a work-around. But I want to find out if it is possible to do it this way. If this is not possible, it seems like quite a dumb restriction.)

Update: I'm using DB2 9.7 LUW.

Improve this question

2 Answers 2

Reset to default
1

If you issue a select, you have to consume the result set somehow, whether it is in a procedure or another application. You can either run a dummy loop within the procedure, like:

for t in (with deleted as (
    select * from old table (delete from my_table where foo > 1)
    )
    select * from new table (insert into archive_table select * from deleted)
) loop
  null;
end loop;

or use an explicit cursor:

declare c1 cursor for with deleted as (
    select * from old table (delete from my_table where foo > 1)
    )
    select * from new table (insert into archive_table select * from deleted);
...
open c1;
close c1;

Note that neither of these is tested.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The first option does not work (common table expressions are not allowed in a loop statement). The second did the trick however. Thanks!
0

Why dont you flip it around? You can INSERT from a SELECT, and you can SELECT the rows from a DELETE.

Improve this answer

3 Comments

There seems to be a limitation preventing you from using the data change statement {select from final table (delete...)} as the source of rows for an insert.
Sorry if my question wasn't clear. When I said "you can't just do an insert based on the results returned", I meant that DB2 arbitrarily prevents you from doing exactly this. Yeah, it would make sense to do it this way, if it were possible. Thanks for taking the time to answer, though.
Sorry, it will teach me to answer before having a cup of coffee in the morning :o/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.