Lock table A in share mode;
insert into table B select * from table A;
delete from table A;
commit;
"Wolfram Roesler" <wr_at_grp.de> wrote in message
news:Xns929DB035AAFC0wrgrpde_at_130.133.1.4...
> 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
Received on Fri Oct 04 2002 - 21:02:00 CDT
