Received: from odin.sinectis.com.ar (IDENT:root@odin.sinectis.com.ar [200.16.183.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA37912 for ; Tue, 18 May 1999 10:41:43 -0400 (EDT) (envelope-from aroizen@sinectis.com.ar) Received: from sinectis.com.ar (IDENT:nobody@ix.sinectis.com.ar [200.5.70.4]) by odin.sinectis.com.ar (8.9.1/8.9.1) with ESMTP id LAA14396 for ; Tue, 18 May 1999 11:41:36 -0300 Sender: root@odin.sinectis.com.ar Message-ID: <37417CE9.B967D174@sinectis.com.ar> Date: Tue, 18 May 1999 11:44:57 -0300 From: Ana Roizen X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.36 i686) MIME-Version: 1.0 To: "pgsql-sql@hub.org" Subject: Updating and null values. Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi! Can anyone help me with this? Say I have two tables : tt(tx1 int4, tx2 oid,ty1 int4, ty2 oid ); xx(x1 int4, x2 oid) yy (y1 int4, y2 oid) 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. Example: Suppose the following instances of my tables: tt = ( ( , 500, ,100), ( , 600, ,200 ), ( , 0, ,300 ) ) xx= ( (2, 500), (3, 600), ( 4, 1000)) yy =( (2,100), (1,200) , (4, 300) ) The result of the query would then be: tt = ( ( 2 , 500, 2 ,100), ( 3 , 600, 1 ,200 ), ( , 0, ,300 ) ) It only updated the first two tuples. I wanted the third tuple to be updated too. It should have the new values ( ,0 ,4, 300), as y1 is not null, so ty1 should now have the same value as y1.(not a null one) Does anyone have an idea on how to solve this? Any help would be great. Thanks. Ana Roizen.