Re: Select into - Mailing list pgsql-sql
| From | Gurjeet Singh |
|---|---|
| Subject | Re: Select into |
| Date | |
| Msg-id | 65937bea0803200503j29b6a7cew806f41c15be1877f@mail.gmail.com Whole thread Raw |
| In response to | Re: Select into (Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>) |
| List | pgsql-sql |
On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis <gavinb@eclinic.com.au> wrote:
Even a small example of what you wanted would have worked.
Anyway, lets see if I can got your problem.
There are two records in your table emp:
id | name | salary
-------------------------
21 | scott | 2000
31 | greg | 3000
So you want to copy all the data from 'scott' row on to 'greg' row, but keep the id (id obviously being your unique identifier).
UPDATE emp
SET (salary, name) = ( (select salary from emp where id = 21 ),
(select name from emp where id = 21) )
where id = 31;
HTH,
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
HI Gurjeet,You're right.But what information do you need to know?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 am happy enough to give you a table schema, if that's required... but I just don't see why it would be needed - but of course am happy to be told something new!
Even a small example of what you wanted would have worked.
Anyway, lets see if I can got your problem.
There are two records in your table emp:
id | name | salary
-------------------------
21 | scott | 2000
31 | greg | 3000
So you want to copy all the data from 'scott' row on to 'greg' row, but keep the id (id obviously being your unique identifier).
UPDATE emp
SET (salary, name) = ( (select salary from emp where id = 21 ),
(select name from emp where id = 21) )
where id = 31;
HTH,
Best regards,
Thanks againGavin BaumanisOn 20/03/2008, at 9:58 PM, Gurjeet Singh wrote:On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:--am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes:> Hi Everyone,insert into <table> select from <table> where ...
>
> I have asked our DBA at work and h is not too sure either... so I
> thought it best to on the list.
>
> Basically, what I am after is a way to copy the contents of one record
> into another.
> Something like select into; but where the destination record already
> exists, as opposed to creating a new record.
He specifically asked for
<snip>
where the destination record already
exists, as opposed to creating a new record.
</snip>
I think an UPDATE with joins would be helpful. Though, it may become lengthy if the tables have too many columns.
Can you post your exact requirement?
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device