Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA42585 for ; Tue, 18 May 1999 11:13:04 -0400 (EDT) (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 LAA19393; Tue, 18 May 1999 11:11:45 -0400 (EDT) To: Ana Roizen cc: "pgsql-sql@hub.org" Subject: Re: [SQL] Updating and null values. In-reply-to: Your message of Tue, 18 May 1999 11:44:57 -0300 <37417CE9.B967D174@sinectis.com.ar> Date: Tue, 18 May 1999 11:11:45 -0400 Message-ID: <19391.927040305@sss.pgh.pa.us> From: Tom Lane Ana Roizen writes: > I want to perform the following query: > UPDATE tt SET tx1 = A.x1, ty1=B.y1 FROM xx A, yy B WHERE tx2 = A.x2 > AND ty2 = B.y2; > This works fine while there's always a matching tuple of xx and yy for > tx2 and ty2. If one of the values doesn't find a matching tuple, then > the whole tt tuple isn't updated. It seems to me that you are asking for the two fields to be updated independently, so why not just do two queries? UPDATE tt SET tx1 = A.x1 FROM xx A WHERE tx2 = A.x2; UPDATE tt SET ty1 = B.y1 FROM yy B WHERE ty2 = B.y2; (You can use begin/end transaction if you want to ensure that no one else can see the intermediate state of the table.) I believe that UPDATE is acting as it should in the example you show. If it worked the way you suggest, there would be no way to achieve the other effect where you *don't* want an update to occur unless matching records exist in both A and B. regards, tom lane