In-Reply-To: <200607071129.35291.gary.stainburn@ringways.co.uk>
References: <200607071129.35291.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <6AA4D8DF-F0BB-47D9-B944-F7D737EAC609@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Update from join
Date: Fri, 7 Jul 2006 08:06:43 -0400
To: Gary Stainburn <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