From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Gavin 'Beau' Baumanis <gavinb(at)eclinic(dot)com(dot)au> |
Cc: | Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org, "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Subject: | Re: Select into |
Date: | 2008-03-20 12:05:12 |
Message-ID: | 47E252F8.6030008@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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)
-> Index Scan 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
From | Date | Subject | |
---|---|---|---|
Next Message | Joe | 2008-03-20 12:10:47 | Re: Select into |
Previous Message | Gurjeet Singh | 2008-03-20 12:03:03 | Re: Select into |