If you do not understand what you are doing, it might be a g= ood idea to find someone in your organization who understands SQL.

You were given the suggestion UPDATE <table> SET <c= olumn list > FROM <from list > … which is documented here:

http://www.postgresql.org/docs/9.0/interactive/sql-update.ht= ml

In the end, your query should probably end up as an update w= ith an inner join in the FROM section. Do you know what the unique in= dexes are on the tables in question? If you do not use unique indexes in the join, or some o= ther way to ensure that there is a one to one correspondence, you will be a= ltering great big patches of data.

If you are performing an update query, which modifies the da= ta, you should be very careful to get it right. It is risky to have s= omeone 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 wil= l not create correct results.

From: pgsql-ge= neral-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of venkat
Sent: Tuesday, October 26, 2010 2:15 AM
To: Alban Hertroys
Cc: pgsql-general@postgresql.org; pgsql-sql@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 a= m not able to understand.Please alter my code.

(select= kasarano from parcelsdata),
   &= nbsp; murabano=3D(select murabano from parcelsd= ata),
   &= nbsp; the_geom =3D (select (st_dump(st_polygoni= ze(st_union(st_boundary(the_geom), the_line)))).geom as the_geom from
   &= nbsp; (select gid,kasarano,murabano,st_linefromtext('LINESTRING('
   &= nbsp; ||(st_xmin(the_geom)-1)::text||'
   &= nbsp; '||(st_ymax(the_geom)-the_length)||',
   &= nbsp; '||st_xmax(the_geom)+1||'
   &= nbsp; '||st_ymax(the_geom)-the_length||')',24047) as the_line, the_g= eom from
   &= nbsp; (select 100 as the_length, * from parcelsdata) a) b
   &= nbsp; where gid =3D 113 GROUP BY gid,kas= arano,murabano)

if i co= mpile above code , its giving me 2 records.. and when i try to update the t= able i am getting using below code...

update = parcelsdata set gid=3D(select random() * 10),
   &= nbsp; kasarano=3D(select kasarano from parcelsd= ata),
   &= nbsp; murabano=3D(select murabano from parcelsd= ata),
   &= nbsp; the_geom =3D (select (st_dump(st_polygoni= ze(st_union(st_boundary(the_geom), the_line)))).geom as the_geom from
   &= nbsp; (select gid,kasarano,murabano,st_linefromtext('LINESTRING('
   &= nbsp; ||(st_xmin(the_geom)-1)::text||'
   &= nbsp; '||(st_ymax(the_geom)-the_length)||',
   &= nbsp; '||st_xmax(the_geom)+1||'
   &= nbsp; '||st_ymax(the_geom)-the_length||')',24047) as the_line, the_g= eom from
   &= nbsp; (select 100 as the_length, * from parcelsdata) a) b
   &= nbsp; where gid =3D 113 GROUP BY gid,kas= arano,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 whe= re I am doing wrong.. guide me how to update those multiple records..I am w= aiting for your great response.

Thanks and Regards,

Venkat

On Tue, Oct 26, 2010 at 1:32 PM, Alban Hertroys <= dalroi@solfertje.stu= dent.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 b= elow query.I am getting error as
>
> "ERROR: more than one row returned by a subquery used as an expre= ssion
> 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 s= elected 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=3D(select random() * 10),
> = kasarano=3D(select kasarano from parcelsdata),
> = murabano=3D(select murabano from parcelsdata),
> = the_geom =3D (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)&= #43;1||'
> '||st_ymax(the_geom)-= the_length||')',24047) as the_line, the_geom from
> (select 100 as the_le= ngth, * from parcelsdata) a) b
> = where gid =3D 113 GROUP BY gid,kasarano,murabano)
>
> where kasarano=3D'1' and murabano=3D'119'

You would rewrite tha= t to, for example:

update parcelsdata
set gid =3D random() * 10,
kasarano =3D pd2.kasarano,
murabano =3D pd2.murabano

from parcelsdata pd2
where id =3D pd2.id -- subs= titute for whatever your primary key/condition is

and kasarano =3D '1'
and murabano =3D '119'

Yeah, I left out the geometry thing as I'm too lazy to figure out where you= r brackets start and end ;)

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:1184,4cc68b1610291250718568!