Re: Select into

From: Joe <dev(at)freedomcircle(dot)net>
To: Gavin 'Beau' Baumanis <gavinb(at)eclinic(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Select into
Date: 2008-03-20 12:10:47
Message-ID: 47E25447.6040109@freedomcircle.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Joe

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gurjeet Singh 2008-03-20 12:14:23 Re: Select into
Previous Message Craig Ringer 2008-03-20 12:05:12 Re: Select into