[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] [GENERAL] How to update multiple rows
From: Dann Corbit <DCorbit () connx ! com>
Date: 2010-10-26 9:27:15
Message-ID: 87F42982BF2B434F831FCEF4C45FC33E42068819 () EXCHANGE ! corporate ! connx ! com
[Download RAW message or body]
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-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 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@solfertje.student.utwente.nl<mailto:dalroi@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<http://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!
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"> <head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.apple-style-span
{mso-style-name:apple-style-span;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
-->
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">If you do not understand what you are doing, it might be a good idea \
to find someone in your organization who understands SQL.<o:p></o:p></span></p> <p \
class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">You were given the suggestion UPDATE <table> SET <column list \
> FROM <from list > … which is documented here:<o:p></o:p></span></p> \
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D"><a href="http://www.postgresql.org/docs/9.0/interactive/sql-update.html \
">http://www.postgresql.org/docs/9.0/interactive/sql-update.html</a><o:p></o:p></span></p>
<p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">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.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">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.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">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<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D">Without both of those qualifications, the query produced will not \
create correct results.<o:p></o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D"><o:p> </o:p></span></p> <p class="MsoNormal"><span \
style="font-size:11.0pt;font-family:"Calibri","sans-serif"; \
color:#1F497D"><o:p> </o:p></span></p> <div style="border:none;border-left:solid \
blue 1.5pt;padding:0in 0in 0in 4.0pt"> <div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span \
style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> \
pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] <b>On \
Behalf Of </b>venkat<br> <b>Sent:</b> Tuesday, October 26, 2010 2:15 AM<br>
<b>To:</b> Alban Hertroys<br>
<b>Cc:</b> pgsql-general@postgresql.org; pgsql-sql@postgresql.org<br>
<b>Subject:</b> Re: [GENERAL] How to update multiple rows<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span style="color:#000099">Dear Alban,</span><o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#000099"> 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.</span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="apple-style-span"><span style="font-size:10.0pt;
font-family:"Arial","sans-serif";color:#000099">(select kasarano \
from parcelsdata),</span></span><span \
style="font-size:10.0pt;font-family:"Arial","sans-serif"; \
color:#000099"><br> <span class="apple-style-span"> \
murabano=(select murabano from \
parcelsdata),</span><br> <span class="apple-style-span"> \
the_geom = (select \
(st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line)))).geom as the_geom \
from</span><br> <span class="apple-style-span"> \
(select \
gid,kasarano,murabano,st_linefromtext('LINESTRING('</span><br> <span \
class="apple-style-span"> \
||(st_xmin(the_geom)-1)::text||'</span><br> <span \
class="apple-style-span"> \
'||(st_ymax(the_geom)-the_length)||',</span><br> <span \
class="apple-style-span"> \
'||st_xmax(the_geom)+1||'</span><br> <span class="apple-style-span"> \
'||st_ymax(the_geom)-the_length||')',24047) \
as the_line, the_geom from</span><br> <span class="apple-style-span"> \
(select 100 as the_length, * from \
parcelsdata) a) b</span><br> <span class="apple-style-span"> \
where gid = 113 GROUP \
BY gid,kasarano,murabano)</span></span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><span class="apple-style-span"><span style="font-size:10.0pt;
font-family:"Arial","sans-serif";color:#000099">if i compile \
above code , its giving me 2 records.. and when i try to update the table i am \
getting using below code...</span></span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="apple-style-span"><span style="font-size:10.0pt;
font-family:"Arial","sans-serif";color:#000099">update \
parcelsdata set gid=(select random() * 10),</span></span><span \
style="font-size:10.0pt; \
font-family:"Arial","sans-serif";color:#000099"><br> <span \
class="apple-style-span"> \
kasarano=(select kasarano from parcelsdata),</span><br> <span \
class="apple-style-span"> \
murabano=(select murabano from parcelsdata),</span><br> <span \
class="apple-style-span"> \
the_geom = (select \
(st_dump(st_polygonize(st_union(st_boundary(the_geom), the_line)))).geom as the_geom \
from</span><br> <span class="apple-style-span"> \
(select \
gid,kasarano,murabano,st_linefromtext('LINESTRING('</span><br> <span \
class="apple-style-span"> \
||(st_xmin(the_geom)-1)::text||'</span><br> <span \
class="apple-style-span"> \
'||(st_ymax(the_geom)-the_length)||',</span><br> <span \
class="apple-style-span"> \
'||st_xmax(the_geom)+1||'</span><br> <span class="apple-style-span"> \
'||st_ymax(the_geom)-the_length||')',24047) \
as the_line, the_geom from</span><br> <span class="apple-style-span"> \
(select 100 as the_length, * from \
parcelsdata) a) b</span><br> <span class="apple-style-span"> \
where gid = 113 GROUP \
BY gid,kasarano,murabano)</span></span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span class="apple-style-span"><span \
style="font-family:"Arial","sans-serif"; color:#000099">I am \
getting below error..</span></span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><span style="color:#CC0000">"ERROR: more than one row \
returned by a subquery used as an expression</span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><span style="color:#CC0000">SQL state: \
21000"</span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#000099">Please let me know where I am doing \
wrong.. guide me how to update those multiple records..I am waiting for your great \
response.</span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="color:#000099">Thanks and \
Regards,</span><o:p></o:p></p> </div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span \
style="color:#000099">Venkat</span><o:p></o:p></p> <div>
<p class="MsoNormal">On Tue, Oct 26, 2010 at 1:32 PM, Alban Hertroys <<a \
href="mailto:dalroi@solfertje.student.utwente.nl">dalroi@solfertje.student.utwente.nl</a>> \
wrote:<o:p></o:p></p> <div>
<p class="MsoNormal" style="margin-bottom:12.0pt">On 26 Oct 2010, at 9:07, venkat \
wrote:<br> <br>
> Dear All,<br>
><br>
> I want to update multiple row in single query.I am trying for below \
query.I am getting error as<br> ><br>
> "ERROR: more than one row returned by a subquery used as an expression<br>
> SQL state: 21000"<o:p></o:p></p>
</div>
<p class="MsoNormal">You're probably looking for UPDATE table FROM other_table.<br>
<br>
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.<o:p></o:p></p>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt"><br>
> Here is my Query.<br>
><br>
> update parcelsdata set gid=(select random() * 10),<br>
> \
kasarano=(select kasarano from parcelsdata),<br> > \
murabano=(select murabano from \
parcelsdata),<br> > \
the_geom = (select (st_dump(st_polygonize(st_union(st_boundary(the_geom), \
the_line)))).geom as the_geom from<br> > \
(select gid,kasarano,murabano,st_linefromtext('LINESTRING('<br> > \
||(st_xmin(the_geom)-1)::text||'<br> > \
\
'||(st_ymax(the_geom)-the_length)||',<br> > \
'||st_xmax(the_geom)+1||'<br> > \
'||st_ymax(the_geom)-the_length||')',24047) as the_line, \
the_geom from<br> > (select 100 \
as the_length, * from parcelsdata) a) b<br> > \
where gid = 113 GROUP BY \
gid,kasarano,murabano)<br> ><br>
> where kasarano='1' and murabano='119'<o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt">You would rewrite that to, for \
example:<br> <br>
update parcelsdata<br>
set gid = random() * 10,<br>
kasarano = pd2.kasarano,<br>
murabano = pd2.murabano<br>
<br>
from parcelsdata pd2<br>
where id = <a href="http://pd2.id" target="_blank">pd2.id</a> -- substitute for \
whatever your primary key/condition is<br> <br>
and kasarano = '1'<br>
and murabano = '119'<br>
<br>
Yeah, I left out the geometry thing as I'm too lazy to figure out where your brackets \
start and end ;)<br> <br>
Alban Hertroys<br>
<span style="color:#888888"><br>
--<br>
Screwing up is an excellent way to attach something to the ceiling.<br>
<br>
<br>
!DSPAM:1184,4cc68b1610291250718568!<br>
<br>
</span><o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic