From: Erik Jones <erik@myemma.com>
To: Joe <dev@freedomcircle.net>
Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Select into
Date: Thu, 20 Mar 2008 10:05:15 -0500
Message-ID: <96BAF397-84BE-4C71-8570-34F13AD482B0@myemma.com> (raw)
In-Reply-To: <47E25447.6040109@freedomcircle.net>
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>
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
>> (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 = t1copy.col2, col3 = t1copy.col3
> from t1 as t1copy
> where t1.col1 = 1 and t1copy.col1 = 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) = (t1copy.col2, t1copy.col3)
from t1 as t1copy
where t1.col =1 and t1copy.col1=3;
Erik Jones
DBA | Emma®
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
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: erik@myemma.com, dev@freedomcircle.net, gavinb@eclinic.com.au
Subject: Re: Select into
In-Reply-To: <96BAF397-84BE-4C71-8570-34F13AD482B0@myemma.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