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

On May 23, 10:34 am, "Frank Swarbrick"
<Frank.Swarbr...@efirstbank.com> wrote:
> 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

>>> On 5/23/2008 at 1:28 PM, in message
<59c28351-0535-4835-bc84-6e1163c6ac5b@p25g2000pri.googlegroups.com>,
jefftyzzer<jefftyzzer@sbcglobal.net> wrote:
> On May 23, 10:34 am, "Frank Swarbrick"
> <Frank.Swarbr...@efirstbank.com> wrote:
>> 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