Re: Select into - Mailing list pgsql-sql

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.