Re: Update from join

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