Task : Delete rows from one table and insert into another
CREATE TABLE Archive LIKE Inventory;
WITH del(Item, Quantity, InvDate)
AS (SELECT Item, Quantity, InvDate FROM OLD TABLE (
DELETE FROM (SELECT Item, Quantity, InvDate, row_number()
OVER(PARTITION BY Item ORDER BY InvDate DESC) AS rn FROM Inventory)
WHERE rn > 1)),
ins(x) AS (SELECT 1 FROM NEW TABLE(INSERT INTO SELECT * FROM del))
SELECT COUNT(1) FROM ins;

Hi
I found the above sql in presentation named 'SQl on fire' amazing
one, and has a lot to do in set processing. But I want to know why
this works and not the one below
insert into newtab (select * from old table (delete from very old
table))

Is it because in the above query, the insert will try to work before
the deletion, and old table cursor population ? By that I can agree
with the above sql, first delete, then cursor then insert into new
table. Or is there any other reason? I didnt get any notes on the
presentation and hence this question.

Thanks and have a great day
Arun S

Arun Srinivasan wrote:
> Task : Delete rows from one table and insert into another
> CREATE TABLE Archive LIKE Inventory;
> WITH del(Item, Quantity, InvDate)
> AS (SELECT Item, Quantity, InvDate FROM OLD TABLE (
> DELETE FROM (SELECT Item, Quantity, InvDate, row_number()
> OVER(PARTITION BY Item ORDER BY InvDate DESC) AS rn FROM Inventory)
> WHERE rn > 1)),
> ins(x) AS (SELECT 1 FROM NEW TABLE(INSERT INTO SELECT * FROM del))
> SELECT COUNT(1) FROM ins;
>
> Hi
> I found the above sql in presentation named 'SQl on fire' amazing
> one, and has a lot to do in set processing. But I want to know why
> this works and not the one below
> insert into newtab (select * from old table (delete from very old
> table))
>
> Is it because in the above query, the insert will try to work before
> the deletion, and old table cursor population ? By that I can agree
> with the above sql, first delete, then cursor then insert into new
> table. Or is there any other reason? I didnt get any notes on the
> presentation and hence this question.
When you check the db2exfmt for the working query you will (should at
least) find that the optimizer produces this streamlined version.
So why can't you write it?

The concept of MODIFYing SQL DATA within a query is fraud with semantic
mine fields mostly related to order of execution.
"common table expressions" (WITH clause) provides a very simple way to
clarify order.
Essentially, when in doubt DB2 will order execution in the order in
which the CTE's are specified.
If/When the SQL Standard adopts the syntax and semantics we can expand
upon in.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

You can write your "move rows to an exception table" in a single SQL,
just not the simple way you would expect it (for the semantic reasons
that Serge mentioned above):

with
tmp1 as (select * from old table (delete from very_old_table)),
tmp2 as (select * from new table(insert into newtab select from tmp1))
select count(*) from tmp2;

Regards,
Miro