move rows from one table to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Frank Swarbrick - 23 May 2008 18:34 GMT

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

Reply to this Message

jefftyzzer - 23 May 2008 20:28 GMT

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?
[quoted text clipped - 28 lines]
> 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

Reply to this Message

Frank Swarbrick - 23 May 2008 22:33 GMT

>>> On 5/23/2008 at 1:28 PM, in message

<59c28351-0535-4835-bc84-6e1163c6ac5b@p25g2000pri.googlegroups.com>,

> On May 23, 10:34 am, "Frank Swarbrick"
> <Frank.Swarbr...@efirstbank.com> wrote:
[quoted text clipped - 38 lines]
> 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

Reply to this Message