Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5D9E22E0098 for ; Thu, 20 Mar 2008 10:13:58 -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 40883-09 for ; Thu, 20 Mar 2008 10:13:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id DF4D12E002D for ; Thu, 20 Mar 2008 10:13:48 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so1060304waf.5 for ; Thu, 20 Mar 2008 06:13:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=VEJIxuuwDjY8w4JbO1A7Di3dJO7h5eoif1Lx3VS2tYk=; b=Z5JGHw4wE7z5zfXSEsJjpz5dvVIJGK3R5O5wwhnN7RPlyn5X5usW0JckYSWhpexbbPasI34zTb1oGy3fwOq6UFw/Bj5XP5fp1OQcnmanmo0FvzXw/nL/FD/Fm1ioAv8suvz3cnVu7jXIOSSNfvv2mTDOYBF+DZQ85k4yucDrFNY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=JRdBpCiaMOz0j+g7v5Au8rQrcQ8/36T9erZ9ns/iT896lWiDb/f/weQ1ksrgnMLOFoCEbEvG0lKiMOqgA6lkfm8U8HECekQxsUyFy7RGuAQv+cT51zMT9WbIVYz3nPQmrapmIshNVlpDJfQazX3lIRkJUtt2ajaZJyVIsZfkkGw= Received: by 10.115.61.1 with SMTP id o1mr3492079wak.94.1206018827365; Thu, 20 Mar 2008 06:13:47 -0700 (PDT) Received: by 10.114.27.16 with HTTP; Thu, 20 Mar 2008 06:13:47 -0700 (PDT) Message-ID: <65937bea0803200613k4593da12hd62233f8a745309@mail.gmail.com> Date: Thu, 20 Mar 2008 18:43:47 +0530 From: "Gurjeet Singh" To: Joe Subject: Re: Select into Cc: "Gavin 'Beau' Baumanis" , pgsql-sql@postgresql.org In-Reply-To: <47E25447.6040109@freedomcircle.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14582_281022.1206018827367" 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-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/143 X-Sequence-Number: 30471 ------=_Part_14582_281022.1206018827367 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Thu, Mar 20, 2008 at 5:40 PM, Joe wrote: > Gavin 'Beau' Baumanis wrote: > > > > The copy is inside the same table, so I don't understand why it (the > > required query ) would require any joins. > > > > Ie. I want to copy the contents of a row (but for the id column - 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. > Except that it doesn't work... Did you try to execute that query; I am assuming not. Best regards, --=20 gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17=B0 29' 34.37"N, 78=B0 30' 59.76"E - Hyderabad * 18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune 37=B0 47' 19.72"N, 122=B0 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device ------=_Part_14582_281022.1206018827367 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Thu, Mar 20, 2008 at 5:40 PM, Joe <dev@freedomcircle.net> wrote:
<= blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 2= 04, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Gavin 'Beau' Baumanis wrote:
>
> The copy is inside the same table, so I don't understand why it (t= he
> required query ) would require any joins.
>
> Ie. I want to copy the contents of a row (but for the id column - 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.

Except that it doesn't work... Did you try to execute that= query; I am assuming not.

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotm= ail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17=B0 29' 34.37&= quot;N, 78=B0 30' 59.76"E - Hyderabad *
18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune
37=B0 47&#= 39; 19.72"N, 122=B0 24' 1.69" W - San Francisco

http://gurjeet.frihost.net

Mail= sent from my BlackLaptop device ------=_Part_14582_281022.1206018827367--