Обсуждение: 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!