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:
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