Re: update from join

I wonder if this works:

update stock s set s_superceded = true
where s.s_updated < (select max(t.s_updated) from stock t where t.s_vin =
s.s_vin)

On Thu, May 14, 2009 at 7:27 AM, Gary Stainburn <
gary(dot)stainburn(at)ringways(dot)co(dot)uk> wrote:

> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>