Re: Atomically moving rows from one table into another

Wolfram Roesler wrote:

> Hello,
>
> suppose I have two tables, tab1 and tab2, created from the same DDL.
> I want to move data from tab1 to tab2 like this: (xxx is some
> where clause)
>
> INSERT INTO tab2 (SELECT * FROM tab1 WHERE xxx);
> DELETE FROM tab1 WHERE xxx;
> COMMIT;
>
> Now, what happens if, after the INSERT yet before the DELETE,
> someone inserts rows into tab1 that match xxx? They will be
> deleted although they have not been transferred to tab2.
>
> Is there a way to atomically move data from one table to
> another that doesn't suffer from this problem?
>
> Thanks for any help
> W. Roesler

RENAME tab1 TO tabx
CREATE TABLE newtab1
INSERT INTO newtab1 FROM tabx
RENAME newtab1 TO tab1

Or some variation.

Or, of course, you could just lock the darned thing in exclusive mode.

Daniel Morgan Received on Fri Oct 04 2002 - 10:36:04 CDT