Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 352AD63377F for ; Thu, 14 May 2009 12:52:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04867-10 for ; Thu, 14 May 2009 12:52:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.27]) by mail.postgresql.org (Postfix) with ESMTP id BEF7B633369 for ; Thu, 14 May 2009 12:52:20 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so929884qwh.61 for ; Thu, 14 May 2009 08:52:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=u3pwJP5Qdech+ssiI+kurcuOF4lxFjrHIwHiO4nWhpk=; b=B+NAaRFMDlwasLGjNFU17pQ3tcfeOpEt8AFQpQM5o4RSr+wk9Hh9xjIPNHVhJuAlDu 8GHwOccRWgbzVqr3EAmklGq77ZbYrf87fknSEC4dhR7pspWZkJX8DMk2J8j4/e6DopbW hnLBTdfYk9LvEX962QHjC57qq1mM7IqDsJN9A= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=qb2knSQZJ0Lf9yFKw6LGid0Vok5+laUuouLdWkS3CLW0d6TLPtSw+apKbhAn4asNRK So/OkLj8EW20J6YbJ3XlLHMJMOA2t+4U7i/0un9xHLaVt9EWsdvtBge1lX3tKgQwGthm EgfUo68rhdAoGLlfcQuBFCcanbnNFoD/0iWZA= MIME-Version: 1.0 Received: by 10.224.45.72 with SMTP id d8mr2807874qaf.155.1242315843092; Thu, 14 May 2009 08:44:03 -0700 (PDT) In-Reply-To: <200905141427.15022.gary.stainburn@ringways.co.uk> References: <200905141427.15022.gary.stainburn@ringways.co.uk> Date: Thu, 14 May 2009 09:44:02 -0600 Message-ID: <5c4ddc540905140844s75ef34escf72d162759af8f6@mail.gmail.com> Subject: Re: update from join From: Rob Sargent To: pgsql-sql@postgresql.org Cc: Gary Stainburn Content-Type: multipart/alternative; boundary=0015175cba1e86c3360469e132d8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/68 X-Sequence-Number: 32560 --0015175cba1e86c3360469e132d8 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 > --0015175cba1e86c3360469e132d8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I wonder if this works:

update stock s set s_superceded =3D=A0 true =
where s.s_updated < (select max(t.s_updated) from stock t where t.s_= vin =3D s.s_vin)



On Thu, May 14, = 2009 at 7:27 AM, Gary Stainburn <gary.stainburn@ringways.co.uk> wr= ote:
I know I should b= e able to do this but my brain's mashed today

I have a stock table with

s_stock_no =A0 =A0 =A0 =A0 =A0 =A0 =A0varchar primary key
s_vin =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 varchar
s_updated =A0 =A0 =A0 =A0 =A0 =A0 =A0 timestamp
s_superceded =A0 =A0 =A0 =A0 =A0 =A0boolean

It is possible for the same vin to exist on stock =A0if we have sold and th= en
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 hav= e
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,
=A0 =A0 =A0 count(s_updated) as numb,
=A0 =A0 =A0 max(s_updated)::timestamp as latest
=A0from =A0stock
=A0group by s_vin) foo
=A0where 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

--0015175cba1e86c3360469e132d8--