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®
[EMAIL PROTECTED]
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
