Re: Select into

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