Atomically moving rows from one table into another

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 - 10:18:15 CDT