Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 6A9D4475948
	for <pgsql-sql@postgresql.org>; Tue,  4 Jun 2002 17:28:38 -0400 (EDT)
Received: from beamish.nsd.ca (unknown [205.150.156.194])
	by postgresql.org (Postfix) with ESMTP id D1B0547511A
	for <pgsql-sql@postgresql.org>; Tue,  4 Jun 2002 17:28:36 -0400 (EDT)
Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id RAA14184;
	Tue, 4 Jun 2002 17:28:27 -0400
X-Authentication-Warning: beamish.nsd.ca: smap set sender to <jllachan@nsd.ca>
	using -f
Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap
	(V2.1/2.1+anti-relay+anti-spam)
	id xma014182; Tue, 4 Jun 02 17:28:09 -0400
Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148])
	by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id RAA09417;
	Tue, 4 Jun 2002 17:28:09 -0400
Message-ID: <3CFD334B.5242BB1B@nsd.ca>
Date: Tue, 04 Jun 2002 17:38:19 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Matthew Price <pricem@juno.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Updating a table from another table
References: <20020604.163820.15423.68377@wm13.nyc.untd.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200206/31
X-Sequence-Number: 7996

Try:

UPDATE customers
SET canceldate = cancels.canceldate
FROM cancels
WHERE customers.custnum = cancels.custnum;

You should also have an index on cancels(custnum).

JLL



Matthew Price wrote:
> 
> Howdy All,
> 
> I need to update many records in one table with data from another table that ultimately gets \copy-ed from an outside source.
> 
> customers:
> custnum varchar(6),
> name text,
> canceldate date
> ...
> 
> cancels:
> custnum varchar(6),
> canceldate date
> 
> What is the right way to update the canceldate in the customers table from canceldate in the cancels table?  Is there a way to use update to iterate rather than making a mass change?
> 
> Thanks in advance,
> Matthew
> 
> ________________________________________________________________
> GET INTERNET ACCESS FROM JUNO!
> Juno offers FREE or PREMIUM Internet access for less!
> Join Juno today!  For your FREE software, visit:
> http://dl.www.juno.com/get/web/.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster