[SQL] How to update multiple rows
Dear All,
I want to update multiple row in single query.I am trying for below
query.I am getting error as
"ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000"
Here is my Query.
update parcelsdata set gid=(select random() * 10),
kasarano=(select kasarano from parcelsdata),
murabano=(select murabano from parcelsdata),
the_geom = (select
(st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line.geom as
the_geom from
(select gid,kasarano,murabano,st_linefromtext('LINESTRING('
||(st_xmin(the_geom)-1)::text||'
'||(st_ymax(the_geom)-the_length)||',
'||st_xmax(the_geom)+1||'
'||st_ymax(the_geom)-the_length||')',24047) as the_line, the_geom from
(select 100 as the_length, * from parcelsdata) a) b
where gid = 113 GROUP BY gid,kasarano,murabano)
where kasarano='1' and murabano='119'
Please let me know.I am waiting for your great response.
Thanks and Regards,
Venkat
Re: [SQL] [GENERAL] How to update multiple rows
Dear Alban,
Thanks for your great response.I am not able to compile the query which you
have given..I am not able to understand.Please alter my code.
(select kasarano from parcelsdata),
murabano=(select murabano from parcelsdata),
the_geom = (select
(st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line.geom as
the_geom from
(select gid,kasarano,murabano,st_linefromtext('LINESTRING('
||(st_xmin(the_geom)-1)::text||'
'||(st_ymax(the_geom)-the_length)||',
'||st_xmax(the_geom)+1||'
'||st_ymax(the_geom)-the_length||')',24047) as the_line,
the_geom from
(select 100 as the_length, * from parcelsdata) a) b
where gid = 113 GROUP BY gid,kasarano,murabano)
if i compile above code , its giving me 2 records.. and when i try to update
the table i am getting using below code...
update parcelsdata set gid=(select random() * 10),
kasarano=(select kasarano from parcelsdata),
murabano=(select murabano from parcelsdata),
the_geom = (select
(st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line.geom as
the_geom from
(select gid,kasarano,murabano,st_linefromtext('LINESTRING('
||(st_xmin(the_geom)-1)::text||'
'||(st_ymax(the_geom)-the_length)||',
'||st_xmax(the_geom)+1||'
'||st_ymax(the_geom)-the_length||')',24047) as the_line,
the_geom from
(select 100 as the_length, * from parcelsdata) a) b
where gid = 113 GROUP BY gid,kasarano,murabano)
I am getting below error..
"ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000"
Please let me know where I am doing wrong.. guide me how to update those
multiple records..I am waiting for your great response.
Thanks and Regards,
Venkat
On Tue, Oct 26, 2010 at 1:32 PM, Alban Hertroys <
dal...@solfertje.student.utwente.nl> wrote:
> On 26 Oct 2010, at 9:07, venkat wrote:
>
> > Dear All,
> >
> > I want to update multiple row in single query.I am trying for below
> query.I am getting error as
> >
> > "ERROR: more than one row returned by a subquery used as an expression
> > SQL state: 21000"
>
> You're probably looking for UPDATE table FROM other_table.
>
> That said, I think your subqueries are rather under-constrained - you don't
> correlate the records in your subqueries to the records you're updating at
> all! The result will be that all your rows will be based on the last row
> selected by each subquery. I can't imagine why you'd want that, so I assume
> you don't.
>
> > Here is my Query.
> >
> > update parcelsdata set gid=(select random() * 10),
> > kasarano=(select kasarano from parcelsdata),
> > murabano=(select murabano from parcelsdata),
> > the_geom = (select
> (st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line.geom as
> the_geom from
> > (select gid,kasarano,murabano,st_linefromtext('LINESTRING('
> > ||(st_xmin(the_geom)-1)::text||'
> > '||(st_ymax(the_geom)-the_length)||',
> > '||st_xmax(the_geom)+1||'
> > '||st_ymax(the_geom)-the_length||')',24047) as the_line,
> the_geom from
> > (select 100 as the_length, * from parcelsdata) a) b
> > where gid = 113 GROUP BY gid,kasarano,murabano)
> >
> > where kasarano='1' and murabano='119'
>
> You would rewrite that to, for example:
>
> update parcelsdata
> set gid = random() * 10,
>kasarano = pd2.kasarano,
>murabano = pd2.murabano
>
> from parcelsdata pd2
> where id = pd2.id -- substitute for whatever your primary key/condition is
>
> and kasarano = '1'
> and murabano = '119'
>
> Yeah, I left out the geometry thing as I'm too lazy to figure out where
> your brackets start and end ;)
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:1184,4cc68b1610291250718568!
>
>
>
[SQL] Different plan for one query problem
Hello!
I have following query:
SELECT
db_oks_zu."tbl_location"."full_address",
db_class."kladrCache"."region",
db_class."kladrCache"."mo",
db_class."kladrCache"."city",
db_class."kladrCache"."street",
db_oks_zu."tbl_location"."house",
db_oks_zu."tbl_position"."number_flat",
((COALESCE(db_oks_zu."tbl_powners"."psurname",''))||('
'))||(COALESCE(db_oks_zu."tbl_powners"."pname",'')))||('
'))||(COALESCE(db_oks_zu."tbl_powners"."ppatronimic",'')))||('
'))||(COALESCE(db_oks_zu."tbl_powners"."pnumdoc",'')) as c8,
db_oks_zu."tbl_register"."invent_number",
db_oks_zu."tbl_rights"."share",
db_oks_zu."tbl_objects_main"."date_modifed",
db_oks_zu."tbl_objects_main"."parent_id" FROM
"db_oks_zu"."tbl_powners"
Left JOIN "db_oks_zu"."lnk_owners" ON
"db_oks_zu"."lnk_owners"."powners_id"="db_oks_zu"."tbl_powners"."powners_id"
Left JOIN "db_oks_zu"."tbl_rights" ON
"db_oks_zu"."tbl_rights"."right_id"="db_oks_zu"."lnk_owners"."right_id"
Left JOIN "db_oks_zu"."tbl_objects_main" ON
"db_oks_zu"."tbl_objects_main"."object_id"="db_oks_zu"."tbl_rights"."object_id"
Left JOIN "reestr_base"."fileObjects" ON
"reestr_base"."fileObjects"."objectId"="db_oks_zu"."tbl_objects_main"."object_id"
Left JOIN "reestr_base"."invFiles" ON
"reestr_base"."invFiles"."id"="reestr_base"."fileObjects"."fileId"
Left JOIN "db_oks_zu"."tbl_register" ON
"db_oks_zu"."tbl_register"."object_id"="db_oks_zu"."tbl_objects_main"."object_id"
Left JOIN "db_oks_zu"."tbl_inventory" ON
"db_oks_zu"."tbl_inventory"."register_id"="db_oks_zu"."tbl_register"."register_id"
Left JOIN "db_oks_zu"."tbl_location" ON
"db_oks_zu"."tbl_location"."parameter_id"="reestr_base"."invFiles"."id"
Left JOIN "db_class"."kladrCache" ON
"db_class"."kladrCache"."code"="db_oks_zu"."tbl_location"."kladr_id"
Left JOIN "db_oks_zu"."tbl_position" ON
"db_oks_zu"."tbl_position"."object_id"="db_oks_zu"."tbl_objects_main"."object_id"
WHERE (reestr_base."invFiles"."placeStore" = '1') AND
(db_oks_zu."tbl_inventory"."organization_id" = '1');
When reestr_base."invFiles"."placeStore" and
db_oks_zu."tbl_inventory"."organization_id" in where clause compared with
'1' or '2' i'm get following query plan (and real execution time is about 10
seconds for 10,000 rows):
QUERY PLAN
Hash Left Join (cost=76024.15..169664.40 rows=76919 width=429)
" Hash Cond: ((tbl_location.kladr_id)::text = (""kladrCache"".code)::text)"
-> Hash Left Join (cost=75094.18..165849.97 rows=76919 width=430)
"Hash Cond: (""invFiles"".id = tbl_location.parameter_id)"
-> Hash Left Join (cost=25195.02..42082.87 rows=886 width=106)
Hash Cond: (tbl_objects_main.object_id =
tbl_position.object_id)
-> Nested Loop (cost=21660.73..38535.64 rows=886 width=117)
-> Nested Loop (cost=21660.73..37302.47 rows=886
width=129)
-> Nested Loop (cost=21660.73..36846.12 rows=894
width=86)
-> Hash Join (cost=21660.73..36458.09
rows=908 width=86)
" Hash Cond: (tbl_rights.object_id =
""fileObjects"".""objectId"")"
-> Seq Scan on tbl_rights
(cost=0.00..12990.66 rows=479366 width=35)
-> Hash (cost=21654.66..21654.66
rows=486 width=51)
-> Hash Join
(cost=18174.09..21654.66 rows=486 width=51)
Hash Cond:
(tbl_inventory.register_id = tbl_register.register_id)
-> Bitmap Heap Scan on
tbl_inventory (cost=593.88..4016.58 rows=14136 width=16)
Recheck Cond:
((organization_id)::text = '1'::text)
-> Bitmap Index
Scan on tbl_inventory_idx1 (cost=0.00..590.35 rows=14136 width=0)
Index Cond:
((organization_id)::text = '1'::text)
-> Hash
(cost=17387.93..17387.93 rows=15382 width=67)
-> Hash Join
(cost=1358.24..17387.93 rows=15382 width=67)
" Hash Cond:
(tbl_register.object_id = ""fileObjects"".""objectId"")"
-> Seq Scan
on tbl_register (cost=0.00..12520.21 rows=447421 width=35)
-> Hash
(cost=1165.97..1165.97 rows=15382 width=32)
-> Hash
Join (cost=242.29..1165.97 rows=15382 width=32)
"
Hash Cond: (""fileObjects"".""fileId"" = ""invFiles"".id)"
"
-> Seq Scan on ""fileObjects"" (cost=0.00..610.30 rows=25530 width=32)"
-> Hash (cost=201.95..201.95 rows=3227 width=16)
"
-> Seq Scan on ""invFiles"" (cost=0.00..201.95 rows=3227 width=16)"
"
Filter: ((
