Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6339B2E01F7 for ; Thu, 20 Mar 2008 12:05:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87446-01 for ; Thu, 20 Mar 2008 12:05:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp166.iad.emailsrvr.com (smtp166.iad.emailsrvr.com [207.97.245.166]) by postgresql.org (Postfix) with ESMTP id 2D82A2E09A2 for ; Thu, 20 Mar 2008 12:05:24 -0300 (ADT) Received: from relay6.relay.iad.emailsrvr.com (localhost [127.0.0.1]) by relay6.relay.iad.emailsrvr.com (SMTP Server) with ESMTP id 9136772B4B4; Thu, 20 Mar 2008 11:05:17 -0400 (EDT) Received: by relay6.relay.iad.emailsrvr.com (Authenticated sender: erik-AT-myemma.com) with ESMTP id 5CE7172BC19; Thu, 20 Mar 2008 11:05:17 -0400 (EDT) Cc: Gavin 'Beau' Baumanis , pgsql-sql@postgresql.org Message-Id: <96BAF397-84BE-4C71-8570-34F13AD482B0@myemma.com> From: Erik Jones To: Joe In-Reply-To: <47E25447.6040109@freedomcircle.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (Apple Message framework v919.2) Subject: Re: Select into Date: Thu, 20 Mar 2008 10:05:15 -0500 References: <94F93C0F-DA95-4435-B253-57C90961DF34@eclinic.com.au> <20080320104922.GB18935@a-kretschmer.de> <65937bea0803200358i290bdc87hdeca777b593b5df1@mail.gmail.com> <786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au> <47E25447.6040109@freedomcircle.net> X-Mailer: Apple Mail (2.919.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/153 X-Sequence-Number: 30481 On Mar 20, 2008, at 7:10 AM, Joe wrote: > Gavin 'Beau' Baumanis wrote: >> >> The copy is inside the same table, so I don't understand why it =20 >> (the required query ) would require any joins. >> >> Ie. I want to copy the contents of a row (but for the id column - =20 >> of course) into a record in the same table. > > I think what you want is something like this: > > Given (col1 being the id or PK): > > col1 | col2 | col3 > ------+------+--------------- > 1 | 123 | first record > 2 | 456 | second record > 3 | 789 | third record > > then > > update t1 set col2 =3D t1copy.col2, col3 =3D t1copy.col3 > from t1 as t1copy > where t1.col1 =3D 1 and t1copy.col1 =3D 3; > > will result in: > > col1 | col2 | col3 > ------+------+--------------- > 1 | 789 | third record > 2 | 456 | second record > 3 | 789 | third record > > So, it is a join ... of a table with a virtual copy of itself. Note that in 8.2.x and above you can write that as: update t1 set (col2, col3) =3D (t1copy.col2, t1copy.col3) from t1 as t1copy where t1.col =3D1 and t1copy.col1=3D3; Erik Jones DBA | Emma=AE erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com