Обсуждение: Select into

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 record  
into another.
Something like select into; but where the destination record already  
exists, as opposed to creating a new record.

Thanks in advance for anything you might come up with.

Warmest regards,
Gavin Baumanis

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 record  
> into another.
> Something like select into; but where the destination record already  
> exists, as opposed to creating a new record.

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


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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 record
> into another.
> Something like select into; but where the destination 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?

--
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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Ie. I want to copy the contents of a row (but for the id
> column - of course) into a record in the same table.

BEGIN;
CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123;
UPDATE tempfoo SET id = 456;
DELETE FROM foo WHERE id = 456;
INSERT INTO foo SELECT * FROM tempfoo;
COMMIT;

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200803200737
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkfiTIYACgkQvJuQZxSWSsiCMwCdESkEe8Hc5xHhJ2B3qX3V7EqX
Z2IAoMy65D2OhdUpYVtfEq182PhfsEfZ
=fx5V
-----END PGP SIGNATURE-----

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.
Maybe you should use FROM clause in the update that references a 
row-valued subquery?

craig=# create table x ( id serial, val integer );
NOTICE:  CREATE TABLE will create implicit sequence "x_id_seq" for 
serial column "x.id"
CREATE TABLE

craig=# insert into x ( val ) values ( 4 ) , ( 6 ) ;
INSERT 0 2

craig=# select * from x;id | val
----+----- 1 |   4 2 |   6
(2 rows)

craig=# update x set val = foundrow.val from ( select val from x where 
id = 2 ) as foundrow where id = 1 ;
UPDATE 1

craig=# select * from x;id | val
----+----- 2 |   6 1 |   6
(2 rows)

craig=# insert into x ( val ) select generate_series(0,10000);
INSERT 0 10001

craig=# explain update x set val = foundrow.val from ( select val from x 
where id = 4123 ) as foundrow where id = 5912 ;                              QUERY PLAN
-------------------------------------------------------------------------Nested Loop  (cost=0.00..16.55 rows=1
width=14) ->  Index Scan using x_id_idx on x  (cost=0.00..8.27 rows=1 width=10)        Index Cond: (id = 5912)  ->
IndexScan using x_id_idx on x  (cost=0.00..8.27 rows=1 width=4)        Index Cond: (public.x.id = 4123)
 
(5 rows)

Will that do the job?

--
Craig Ringer

Hi Everyone,

I want to thank everyone for their help / suggestions...
I really appreciate it.

Though I think I have found a winner.

> craig=# update x set val = foundrow.val from ( select val from x  
> where id = 2 ) as foundrow where id = 1 ;
> UPDATE 1
>

Very elegant, very clean...
Very nice!

Thanks!!!!

>
>
>> craig=# update x set val = foundrow.val from ( select val from x 
>> where id = 2 ) as foundrow where id = 1 ;
>> UPDATE 1
>>
>
Thinking about it, it'd actually be better written as:

UPDATE x SET val = foundrow.val FROM ( SELECT val FROM x AS x2 WHERE 
x2.id = 2 ) AS foundrow WHERE id = 1;

... because it's nicer to use a table alias for x within the subquery 
and elimate any ambiguity for the reader about which "id" you're 
referring to. After all, it's also valid to reference the "id "field of 
the "x" outside the subquery within it, like in the following valid but 
rather nonsensical query:

UPDATE x SET val = (SELECT id+1) WHERE id = 1;

Using the table alias will not change the query plan at all, it just 
makes the reference to "id" within the subquery unambiguous to the reader.

Sorry for the repeat post.

--
Craig Ringer

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

Gurjeet Singh wrote:
> Except that it doesn't work... Did you try to execute that query; I am
> assuming not.
>   
It does, or at least a query written to work the same way works fine for 
me. Not only that, but at least in the presence of a unique index the 
query planner optimises it to the same query plan as the one I proposed.
From my earlier test data:

craig=# update x set val = x2.val from x as x2 where x.id = 1000 and 
x2.id = 1024;
UPDATE 1
craig=# select * from x where id in (1000,1024); id  | val
------+------1024 | 10211000 | 1021
(2 rows)

craig=# explain update x set val = x2.val from x as x2 where x.id = 1000 
and x2.id = 1024;                               QUERY PLAN
---------------------------------------------------------------------------Nested Loop  (cost=0.00..16.55 rows=1
width=14) ->  Index Scan using x_id_idx on x  (cost=0.00..8.27 rows=1 width=10)        Index Cond: (id = 1000)  ->
IndexScan using x_id_idx on x x2  (cost=0.00..8.27 rows=1 width=4)        Index Cond: (x2.id = 1024)
 
(5 rows)

The above query actually executes slightly faster, presumably because 
the query planner has to do less work to reach the same point than it 
does with the subquery-based one I proposed. You should probably use 
this one instead of the subquery one.

--
Craig Ringer

Gurjeet Singh wrote:
> Except that it doesn't work... Did you try to execute that query; I am 
> assuming not.
Of course I did, do you think I create results by editing them into my 
email?

The script:

delete from t1;
insert into t1 values (1, 123, 'first record');
insert into t1 values (2, 456, 'second record');
insert into t1 values (3, 789, 'third record');
select * from t1;
update t1  set col2 = t1copy.col2, col3 = t1copy.col3
from t1 as t1copy
where t1.col1 = 1 and t1copy.col1 = 3;
select * from t1;
select version();

The output:

DELETE 3
INSERT 0 1
INSERT 0 1
INSERT 0 1col1 | col2 |     col3
------+------+---------------   1 |  123 | first record   2 |  456 | second record   3 |  789 | third record
(3 rows)

UPDATE 1col1 | col2 |     col3
------+------+---------------   2 |  456 | second record   3 |  789 | third record   1 |  789 | third record
(3 rows)
                               version
------------------------------------------------------------------------PostgreSQL 8.1.9 on i686-pc-linux-gnu, compiled
byGCC gcc (GCC) 4.0.3
 
(1 row)

And BTW, I also tried your UPDATE SET (salary, name)  but that only 
works on PG 8.2 and above.  I don't see why my query would fail in 
subsequent releases.

Joe

On Thu, Mar 20, 2008 at 7:08 PM, Joe <<a href="mailto:dev@freedomcircle.net">dev@freedomcircle.net</a>> wrote:<br
/><divclass="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt
0pt0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">Gurjeet Singh wrote:<br /> > Except that it doesn't work...
Didyou try to execute that query; I am<br /> > assuming not.<br /></div>Of course I did, </blockquote></div><br />My
bad...I did not run your query either, and based my assumption on my previous attempt that had thrown an error. Here's
somethingsimilar to what had failed in my previous attemt:<br /><br />update t1  <b>as e1</b> set e1.col2 =
t1copy.col2,e1.col3 = t1copy.col3<br />from t1 as t1copy<br />where e1.col1 = 1 and t1copy.col1 = 3;<br /><br /><br
/>   As you can see, I had aliased the main table t1, and hence the error.<br /><br />Best regards,<br clear="all"
/><br/>-- <br />gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br
/><br/>EnterpriseDB <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />17° 29' 34.37"N,
78°30' 59.76"E - Hyderabad *<br />18° 32' 57.25"N, 73° 56' 25.42"E - Pune<br />37° 47' 19.72"N, 122° 24' 1.69" W - San
Francisco<br/><br /><a href="http://gurjeet.frihost.net">http://gurjeet.frihost.net</a><br /><br />Mail sent from my
BlackLaptopdevice  

On Mar 20, 2008, at 7:10 AM, Joe wrote:

> 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.

Note that in 8.2.x and above you can write that as:

update t1
set (col2, col3) = (t1copy.col2, t1copy.col3)
from t1 as t1copy
where t1.col =1 and t1copy.col1=3;

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com