Re: Select into

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