Thread: Update from join

I know this is probably a FAQ but Google etc hasn't helped.

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.

goole=# \d test1               Table "public.test1"    Column      |         Type          | Modifiers
-----------------+-----------------------+-----------ud_id           | integer               | not nullud_registration
|character varying(20) |ud_stock        | character varying(20) |
 
Indexes:   "test1_pkey" PRIMARY KEY, btree (ud_id)

goole=# \d test2             Table "public.test2"  Column   |         Type          | Modifiers
------------+-----------------------+-----------s_stock_no | character varying(8)  | not nulls_regno    | character
varying(12)|
 
Indexes:   "test2_pkey" PRIMARY KEY, btree (s_stock_no)

goole=# select ud.ud_id, ud.ud_registration, ud.ud_stock, s.s_stock_no  from test1 ud, test2 s  where
upper(ud.ud_registration)= upper(s.s_regno) and        upper(ud.ud_stock) ~ '^[NU][LD]$';ud_id | ud_registration |
ud_stock| s_stock_no
 
-------+-----------------+----------+------------ 2359 | YF06YMT         | NL       | NL6321 2397 | YF06YNC         |
NL      | NL6334 2400 | YB06MJX         | ND       | ND8402 2422 | YH06VGJ         | ND       | ND9055 2380 | YF06ZKC
     | ND       | ND9566 2447 | YB06MHX         | ND       | ND9661 2132 | YC06RZM         | ND       | ND9527 2429 |
YB06SFE        | ND       | ND9611 2448 | YB06PXV         | ND       | ND9689 2417 | YF06MXN         | ND       |
ND90122489 | YB06HHM         | ND       | ND9542 2456 | YB06SFJ         | ND       | ND9675 1666 | YC06RYR         | ND
     | NH310 2455 | YB06ZFH         | ND       | ND9754 2508 | YF06GWU         | NL       | NL6245 2655 | YC06SDV
 | ND       | ND9270 2591 | YF06OJM         | NL       | NL6351 2627 | YC06SGX         | ND       | ND9057 1795 |
YC06SGX        | ND       | ND9057 2634 | YB06KHT         | NL       | NL6450 2620 | YF06ZKD         | ND       |
ND9236
(21 rows)

-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

On Friday 07 July 2006 11:29, Gary Stainburn wrote:
> I know this is probably a FAQ but Google etc hasn't helped.
>
> 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 came up with:

goole=# update test1 set ud_stock=(select s_stock_no from test2 where 
test1.ud_registration = test2.s_regno) where ud_stock ~ '^[NU][LD]$';
UPDATE 679

but as you can see, it updated a hell of a lot more than 21 rows.

Can anyone improve on this?

Gary
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000     

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