Skip site navigation (1) Skip section navigation (2)

Re: Select into

From: Erik Jones <erik(at)myemma(dot)com>
To: Joe <dev(at)freedomcircle(dot)net>
Cc: Gavin 'Beau' Baumanis <gavinb(at)eclinic(dot)com(dot)au>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Select into
Date: 2008-03-20 15:05:15
Message-ID: 96BAF397-84BE-4C71-8570-34F13AD482B0@myemma.com (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)myemma(dot)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




In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2008-03-20 15:11:34
Subject: Re: Rollback locks table - why?
Previous:From: Jan PetersDate: 2008-03-20 15:00:00
Subject: Re: Rollback locks table - why?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group