Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA83681 for ; Tue, 30 Nov 1999 10:23:17 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA20829; Tue, 30 Nov 1999 10:22:43 -0500 (EST) To: Patrick JACQUOT cc: pgsql-sql@postgresql.org Subject: Re: [SQL] found a way to update a table with data from another one In-reply-to: Your message of Tue, 30 Nov 1999 13:07:26 +0100 <3843BDFD.4EF1BACE@anpe.fr> Date: Tue, 30 Nov 1999 10:22:43 -0500 Message-ID: <20827.943975363@sss.pgh.pa.us> From: Tom Lane Patrick JACQUOT writes: > I found a simpler way to do the job I wanted done > UPDATE t1 SET balance = balance+t2.amount WHERE EXISTS (SELECT * > FROM t2 WHERE t2.id = t1.id) > DOES WORK PROPERLY. Does it? I don't think that this will do what you are expecting, because t2 in the outer query is not the same table reference as t2 in the inner query --- there's an implicit FROM t2 in the outer query. What this'll actually do is perform an unrestricted join of *all* rows in t2 to each row in t1 that meets the WHERE condition (ie, has some matching row in t2). Because of the visibility rules for updates, only one of the joined pairs for each t1 row will actually get into the final result --- but it's unlikely to be the one you want. When I tried it, it seemed the first row to be processed in the t2 table got added to all the t1 rows that had matches: regression=> select * from t1; id|balance --+------- 1| 100 2| 1000 3| 0 (3 rows) regression=> select * from t2; id|amount --+------ 1| 44 2| 55 (2 rows) regression=> UPDATE t1 SET balance = balance+t2.amount WHERE EXISTS (SELECT * regression-> FROM t2 WHERE t2.id = t1.id); UPDATE 2 regression=> select * from t1; id|balance --+------- 3| 0 1| 144 2| 1044 (3 rows) I suspect the effect you really want is much simpler: UPDATE t1 SET balance = balance+amount FROM t2 WHERE t1.id = t2.id; That gives me regression=> select * from t1; id|balance --+------- 3| 0 1| 188 2| 1099 (3 rows) so this time the amounts went to the proper places... regards, tom lane