Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E7A352E00C6 for ; Thu, 20 Mar 2008 09:03:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 78862-10 for ; Thu, 20 Mar 2008 09:02:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.226]) by postgresql.org (Postfix) with ESMTP id 2B88D2E00C4 for ; Thu, 20 Mar 2008 09:03:05 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 76so877036wra.10 for ; Thu, 20 Mar 2008 05:03:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=qJzKuycoDj2Z4xVvq1dwUM3AnTMlpsSLpcmJafIxXxE=; b=eQUPQeDyeVBma9S2CZZphfuUfOTH3203AaTywN8TxOdrmabzGM6yBfbQn9clQXAj439b89RJ9GgdeLgBVHF/nGfkfHCTXsy7ZNEooKNQzciLAS8FS9omIUHP7a72R6hW8+O23dZ3WcekINRqhZ/srU3XXJ8NtEq9ci5C+1Srglc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qPJMBiJDzuhhxkh5RZi6n81w3HgOmG/S+6ODh3GCzPOULxf6mpeBIXfgwEYQkqmbQrjUWyMhFoY0UGb6WChizrENzzrMz0CyAJu6LlJKOa1bdxKwSi3sdIp0AXOlBspr71uzHdMuHBFxDuPN/komkux4HENyaJlE9pohzW1Ig+w= Received: by 10.114.52.1 with SMTP id z1mr3014956waz.62.1206014583479; Thu, 20 Mar 2008 05:03:03 -0700 (PDT) Received: by 10.114.27.16 with HTTP; Thu, 20 Mar 2008 05:03:03 -0700 (PDT) Message-ID: <65937bea0803200503j29b6a7cew806f41c15be1877f@mail.gmail.com> Date: Thu, 20 Mar 2008 17:33:03 +0530 From: "Gurjeet Singh" To: "Gavin 'Beau' Baumanis" Subject: Re: Select into Cc: pgsql-sql@postgresql.org, "A. Kretschmer" In-Reply-To: <786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14270_22260227.1206014583509" References: <94F93C0F-DA95-4435-B253-57C90961DF34@eclinic.com.au> <20080320104922.GB18935@a-kretschmer.de> <65937bea0803200358i290bdc87hdeca777b593b5df1@mail.gmail.com> <786D2B38-CE0A-41BF-9E70-4DEB22F8D9EF@eclinic.com.au> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/137 X-Sequence-Number: 30465 ------=_Part_14270_22260227.1206014583509 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis < gavinb@eclinic.com.au> wrote: > 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 kee= p the id (id obviously being your unique identifier). UPDATE emp SET (salary, name) =3D ( (select salary from emp where id =3D 21 ), (select name from emp where id =3D 21) ) where id =3D 31; HTH, Best regards, > Thanks again > > Gavin Baumanis > > > > On 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, > > > > > > 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 recor= d > > > into another. > > > Something like select into; but where the destination record already > > > exists, as opposed to creating a new record. > > > > insert into select from
where ... > > > > > He specifically asked for > > > > where the destination record already > exists, as opposed to creating a new record. > > > > > 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=B0 29' 34.37"N, 78=B0 30' 59.76"E - Hyderabad * > 18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune > 37=B0 47' 19.72"N, 122=B0 24' 1.69" W - San Francisco > > http://gurjeet.frihost.net > > Mail sent from my BlackLaptop device > > > --=20 gurjeet[.singh]@EnterpriseDB.com singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17=B0 29' 34.37"N, 78=B0 30' 59.76"E - Hyderabad * 18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune 37=B0 47' 19.72"N, 122=B0 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device ------=_Part_14270_22260227.1206014583509 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis <gavinb@eclinic.com.au> wrote:
<= div class=3D"gmail_quote">
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 i= d 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 wan= ted 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

S= o you want to copy all the data from 'scott' row on to 'greg= 9; row, but keep the id (id obviously being your unique identifier).

UPDATE emp
SET (salary, name)  =3D ( (select salary from emp wh= ere id =3D 21 ),
         &= nbsp;           &nbs= p;             = (select name from emp where id =3D 21) )
where id =3D 31; 

= HTH,

Best regards,

<= div>
Thanks again

Gavin Baumanis

<= /div>


On 20/03/2008, at 9:58 PM, Gurjeet Singh = wrote:
On Thu, Mar 20, 2008 at 4:19 PM, A. Kre= tschmer <andreas.kretschmer@schollglas.com> wrote:
am  Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin = 9;Beau' Baumanis folgendes:
> Hi Everyone,
>
> 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 o= f one record
> into another.
> Something like select into; but where the dest= ination record already
> exists, as opposed to creating a new record= .

insert into <table> select from <table> where = ...


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?

<= /div>--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | ho= tmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

17=B0 29' 34.37"N, 78=B0 30' 59.76"E - Hyderabad * 18=B0 32' 57.25"N, 73=B0 56' 25.42"E - Pune
37=B0 47= ' 19.72"N, 122=B0 24' 1.69" W - San Francisco

http://gurjeet.frihost= .net

Mail sent from my BlackLaptop device

=



--
gurjeet[.si= ngh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yah= oo }.com

EnterpriseDB http://www.enterpr= isedb.com

17=B0 29' 34.37"N, 78=B0 30' 59.76"E= - Hyderabad *
18=B0 32' 57.25"N, 73=B0 56' 25.42"E - = Pune
37=B0 47' 19.72"N, 122=B0 24' 1.69" W - San Francisco
=
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device ------=_Part_14270_22260227.1206014583509--