Received: from dsh.de (firewall-user@neptun.sns-felb.debis.de [53.122.101.2]) by hub.org (8.9.1/8.9.1) with ESMTP id EAA14754 for ; Mon, 26 Oct 1998 04:47:04 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by dsh.de; id KAA22331; Mon, 26 Oct 1998 10:49:21 +0100 (MET) Received: from dshmail.dsh.de(53.47.15.3) by neptun.dsh.de via smap (3.2) id xma022047; Mon, 26 Oct 98 10:49:00 +0100 Received: from mail1.hh1.dsh.de (mail1.hh1.dsh.de [53.47.9.5]) by dshmail.dsh.de (8.8.7/8.8.7) with ESMTP id KAA18720; Mon, 26 Oct 1998 10:46:35 +0100 (MET) Received: from mars.SAPserv.Hamburg.dsh.de (root@mail5.hh1.dsh.de [53.2.168.17]) by mail1.hh1.dsh.de (8.8.7/8.8.7) with SMTP id KAA14766; Mon, 26 Oct 1998 10:49:06 +0100 Received: from orion.SAPserv.Hamburg.dsh.de by mars.SAPserv.Hamburg.dsh.de with smtp for <> id m0zXgvG-000B5AC; Mon, 26 Oct 98 08:18 MET Received: by orion.SAPserv.Hamburg.dsh.de for les@Mcs.Net id m0zXjTi-000EBPC; Mon, 26 Oct 98 11:02 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] idiom to move records? To: les@Mcs.Net (Leslie Mikesell) Date: Mon, 26 Oct 1998 11:02:22 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <199810260531.XAA14075@Mars.mcs.net> from "Leslie Mikesell" at Oct 25, 98 11:31:05 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text > > 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. Use a transaction and lock the source table first. BEGIN TRANSACTION; LOCK TABLE src_table; INSERT INTO dest_table SELECT * FROM src_table WHERE ...; DELETE FROM src_table WHERE ...; COMMIT TRANSACTION; No other backend can add, modify or remove rows to/in/from src_table while you have a lock on it. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #