move rows from one table to another

[Frank Swarbrick]

Maybe a simple question, but what is the best way to move (not copy!) data
from one table to another?
Meaning, I want to insert a set of rows from one table to another *and*
delete all of the same rows from the source table.

I tried the following, but DB2 does not allow it:

INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM OLD TABLE (
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE
);

DB2 says:
SQL20165N An SQL data change statement within a FROM clause is not allowed
in the context in which it was specified.

Obviously I can do this:
INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

But I thought it might be better to do both in a single statement.
(Maybe not!)

Thanks,
Frank

[jefftyzzer]

On May 23, 10:34 am, "Frank Swarbrick"
<[e-mail]:

Maybe a simple question, but what is the best way to move (not copy!) data
from one table to another?
Meaning, I want to insert a set of rows from one table to another *and*
delete all of the same rows from the source table.

I tried the following, but DB2 does not allow it:

INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM OLD TABLE (
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE
);

DB2 says:
SQL20165N An SQL data change statement within a FROM clause is not allowed
in the context in which it was specified.

Obviously I can do this:
INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

But I thought it might be better to do both in a single statement.
(Maybe not!)

Thanks,
Frank

Frank:

Have a look at slide 16 of part two of Serge's "SQL on Fire"
presentation. You can do the insert from a delete, but you have to do
it via a CTE.

--Jeff

[Frank Swarbrick]

>>On 5/23/2008 at 1:28 PM, in message
<59**********************************@p25g2000pri. googlegroups.com>,
jefftyzzer<je********@sbcglobal.netwrote:

On May 23, 10:34 am, "Frank Swarbrick"
<[e-mail]:

>Maybe a simple question, but what is the best way to move (not copy!)

data

>from one table to another?
Meaning, I want to insert a set of rows from one table to another *and*
delete all of the same rows from the source table.

I tried the following, but DB2 does not allow it:

INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM OLD TABLE (
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE
);

DB2 says:
SQL20165N An SQL data change statement within a FROM clause is not

allowed

>in the context in which it was specified.

Obviously I can do this:
INSERT INTO FILM_TRANSACTIONS2
SELECT *
FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = CURRENT_DATE;

But I thought it might be better to do both in a single statement.
(Maybe not!)

Thanks,
Frank

Frank:

Have a look at slide 16 of part two of Serge's "SQL on Fire"
presentation. You can do the insert from a delete, but you have to do
it via a CTE.

Great! I don't know that I ever would have come up with it on my own, but
here's what I have now.

WITH
DEL(brch_nbr, acct_nbr, last_stmt_date, post_date, amount, serial_nbr
, sequence_nbr, post_flag, null_test, update_date) AS (
SELECT *
FROM OLD TABLE (
DELETE FROM FILM.FILM_TRANSACTIONS
WHERE POST_DATE = '09/14/2006'
)
)
, INS(x) AS (
SELECT 1
FROM NEW TABLE (
INSERT INTO FILM.FILM_TRANSACTIONS2
SELECT *
FROM DEL
)
)
SELECT COUNT(1)
FROM INS;

Works like a charm!

Thanks,
Frank