From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>
Subject: Re: update from join
Date: Thu, 14 May 2009 09:44:02 -0600
Message-ID: <5c4ddc540905140844s75ef34escf72d162759af8f6@mail.gmail.com> (raw)
In-Reply-To: <200905141427.15022.gary.stainburn@ringways.co.uk>
References: <200905141427.15022.gary.stainburn@ringways.co.uk>
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.stainburn@ringways.co.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com, gary.stainburn@ringways.co.uk
Subject: Re: update from join
In-Reply-To: <5c4ddc540905140844s75ef34escf72d162759af8f6@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox