From: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql@postgresql.org
Subject: update from join
Date: Thu, 14 May 2009 14:27:14 +0100
Message-ID: <200905141427.15022.gary.stainburn@ringways.co.uk> (raw)
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
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: gary.stainburn@ringways.co.uk
Subject: Re: update from join
In-Reply-To: <200905141427.15022.gary.stainburn@ringways.co.uk>
* 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