Re: How to update multiple rows

[venkat <ven.tammineni@xxxxxxxxx>]

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 <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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!