Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.9.1/8.9.1) with ESMTP id EAA13371 for ; Mon, 26 Oct 1998 04:33:22 -0500 (EST) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.5/8.8.5) with ESMTP id LAA25912; Mon, 26 Oct 1998 11:33:01 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <199810260531.XAA14075@Mars.mcs.net> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 26 Oct 1998 11:34:41 +0200 To: Leslie Mikesell From: Herouth Maoz Subject: Re: [SQL] idiom to move records? Cc: pgsql-sql@postgreSQL.org At 7:31 +0200 on 26/10/98, Leslie Mikesell wrote: > Is there a handy way to move a set of records to a different > table in sql? I'd like to do this based on a WHERE clause > and have it work atomically such that it will not lose new > records added between the step that copies to the other table > and deleting the copied records. Do it in a transaction, then. Assuming that the target table is defined (not a temporary table you create ad-hoc), you do: BEGIN TRANSACTION; LOCK TABLE source_table; INSERT INTO target_table (col1, col2, col3) SELECT (expr1, expr2, expr3) FROM source_table WHERE ; DELETE FROM source_table WHERE ; COMMIT; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma