From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: pgsql-sql@postgresql.org
Subject: update from join
Date: Thu, 14 May 2009 14:27:14 +0100
User-Agent: KMail/1.9.10
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200905141427.15022.gary.stainburn@ringways.co.uk>

I know I should be able to do this but my brain's mashed today

I have a stock table with 

s_stock_no		varchar primary key 
s_vin			varchar
s_updated		timestamp
s_superceded		boolean

It is possible for the same vin to exist on stock  if we have sold and then 
bought back a vehicle, e.g. as a part exchange.

Every time a vehicle is inserted/updated the s_updated field is update.

How can I update the table so that for each s_vin, if a record does not have 
the most recent s_updated value, s_superceded is set to true?

I can get the most recent value by running:

select * from (select s_vin,
       count(s_updated) as numb, 
       max(s_updated)::timestamp as latest 
  from  stock 
  group by s_vin) foo
  where numb > 1;


but I can't seem to get how I can convert this to an update statement. The num 
> 1 simply removed all vehicles with only one record.

I seem to think I need an update..... from..... statement

-- 
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