From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ana Roizen <aroizen@sinectis.com.ar>
Cc: pgsql-sql@hub.org <pgsql-sql@hub.org>
Subject: Re: [SQL] Updating and null values.
Date: Tue, 18 May 1999 11:11:45 -0400
Message-ID: <19391.927040305@sss.pgh.pa.us> (raw)
In-Reply-To: <37417CE9.B967D174@sinectis.com.ar>
Ana Roizen <aroizen@sinectis.com.ar> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, aroizen@sinectis.com.ar, pgsql-sql@hub.org
Subject: Re: [SQL] Updating and null values.
In-Reply-To: <19391.927040305@sss.pgh.pa.us>
References: <37417CE9.B967D174@sinectis.com.ar> <19391.927040305@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox