[GENERAL] 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: [GENERAL] How to update multiple rows
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:737,4cc68b1c10291756917282!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [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!
Re: [GENERAL] How to update multiple rows
If you do not understand what you are doing, it might be a good idea to find
someone in your organization who understands SQL.
You were given the suggestion UPDATE table SET column list FROM from list
... which is documented here:
http://www.postgresql.org/docs/9.0/interactive/sql-update.html
In the end, your query should probably end up as an update with an inner join
in the FROM section. Do you know what the unique indexes are on the tables in
question? If you do not use unique indexes in the join, or some other way to
ensure that there is a one to one correspondence, you will be altering great
big patches of data.
If you are performing an update query, which modifies the data, you should be
very careful to get it right. It is risky to have someone else write your
query for you, because it is your organization that is intimately familiar with
your data.
Do you have any SQL experts in your company? These two kinds of knowledge are
essential: 1) Knowledge of SQL 2) Knowledge of your company's data
Without both of those qualifications, the query produced will not create
correct results.
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of venkat
Sent: Tuesday, October 26, 2010 2:15 AM
To: Alban Hertroys
Cc: pgsql-general@postgresql.org; pgsql-...@postgresql.org
Subject: Re: [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.nlmailto: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
