From: Michael Glaesemann <grzm@seespotcode.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Update from join
Date: Fri, 7 Jul 2006 08:06:43 -0400
Message-ID: <6AA4D8DF-F0BB-47D9-B944-F7D737EAC609@seespotcode.net> (raw)
In-Reply-To: <200607071129.35291.gary.stainburn@ringways.co.uk>
References: <200607071129.35291.gary.stainburn@ringways.co.uk>
On Jul 7, 2006, at 6:29 , Gary Stainburn wrote:
> I have two tables, both with stock number and registration number in.
> The second table always has the correct stock number, the first
> doesn't.
>
> I want to copy the data across where the stock number is missing.
> The select
> with join shows the rows requiring update, but I can't think how to
> do the
> update.
I think this should do the trick for you:
UPDATE test1
SET ud_stock = s_stock_no
FROM test2
WHERE ud_registration = s_regno
AND ud_stock IS NULL -- limits update just to cases where
ud_stock IS NULL
This statement also shows the WHERE clause doing double duty: it
contains both a JOIN condition (ud_registration = s_regno) and a
restriction (AND ud_stock IS NULL). In a SELECT statement I like to
keep these separate, something like:
SELECT ud_stock, s_stock_no
FROM test1
JOIN test2 ON (ud_registration = s_regno)
WHERE ud_stock IS NULL
However, the UPDATE syntax doesn't provide for this. (See http://
www.postgresql.org/docs/current/interactive/sql-update.html for more
details.)
In your SELECT example you've got a slightly different join condition
and an additional restriction in the WHERE clause. You may want to
add these to the UPDATE statement if these are necessary. To
illustrate my point about separating restriction from join
conditions, this is how you could rewrite your SELECT:
SELECT ud.ud_id
, ud.ud_registration
, ud.ud_stock
, s.s_stock_no
FROM test1 ud
JOIN test2 s ON (upper(ud.ud_registration) = upper(s.s_regno))
WHERE upper(ud.ud_stock) ~ '^[NU][LD]$';
The AND ud_stock IS NULL condition isn't really necessary, as you
said the registration numbers are always the same--without the IS
NULL the UPDATE will just overwrite the ud_stock number with the same
value. However, depending on your table size and the indexes you have
on the tables, it might perform better with the IS NULL condition.
Hope this helps.
Michael Glaesemann
grzm seespotcode net
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: grzm@seespotcode.net, gary.stainburn@ringways.co.uk
Subject: Re: Update from join
In-Reply-To: <6AA4D8DF-F0BB-47D9-B944-F7D737EAC609@seespotcode.net>
* 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