Date: Thu, 20 Mar 2008 08:10:47 -0400
From: Joe <dev@freedomcircle.net>
Subject: Re: Select into
In-reply-to: <786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au>
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
Cc: pgsql-sql@postgresql.org
Message-id: <47E25447.6040109@freedomcircle.net>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
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>
User-Agent: Thunderbird 1.5.0.9 (X11/20080117)

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.

Joe