need clean way to copy col vals from one rec to another

On 10 Feb 2010, at 17:28, Gauthier, Dave wrote:

create table foo (name text, company text, job text);
insert into foo (name,company,job) values (�joe�,�ge�,�engineer�);
insert into foo (name) values (�sue�);

What I want to do is map joe�s company and job over to the sue record, ending up with....
�sue� �ge� �engineer�

Is there a quick/clever.efficient way to do this?

UPDATE foo SET company = joe.company, job = joe.job
FROM foo AS joe
WHERE foo.name = 'sue'
AND joe.name = 'joe';

You could also do this on insert by using:

INSERT INTO foo (name, company, job)
SELECT 'sue', joe.company, joe.job
FROM foo AS joe
WHERE joe.name = 'joe';

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b72e2dd10446151245148!

Outstanding !
Thanks Alban.

-----Original Message-----
From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
Sent: Wednesday, February 10, 2010 11:46 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need clean way to copy col vals from one rec to another

On 10 Feb 2010, at 17:28, Gauthier, Dave wrote:

create table foo (name text, company text, job text);
insert into foo (name,company,job) values ('joe','ge','engineer');
insert into foo (name) values ('sue');

What I want to do is map joe's company and job over to the sue record, ending up with....
'sue' 'ge' 'engineer'

Is there a quick/clever.efficient way to do this?

UPDATE foo SET company = joe.company, job = joe.job
FROM foo AS joe
WHERE foo.name = 'sue'
AND joe.name = 'joe';

You could also do this on insert by using:

INSERT INTO foo (name, company, job)
SELECT 'sue', joe.company, joe.job
FROM foo AS joe
WHERE joe.name = 'joe';

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:1015,4b72e2dc10441976818836!