[SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause
Good morning, I have a function argument blah of type text containing something like 33,44,55,66 . Can I cast it in some way to use it in an IN clause as integers like UPDATE foo SET x = y WHERE id IN ( blah ); or need I revert to dynamic SQL (EXECUTE...) ? Thanks, Axel --- [email protected] PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [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:877,4cc68b2c10291290412564!
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [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 SET FROM ... 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: [email protected] [mailto:[email protected]] On Behalf Of venkat Sent: Tuesday, October 26, 2010 2:15 AM To: Alban Hertroys Cc: [email protected]; [email protected] 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 mailto:[email protected]>> 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 =
Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause
Hey Axel, How about this solution: UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; ? 2010/10/25 Axel Rau > Good morning, > > I have a function argument blah of type text containing something like >33,44,55,66 > . Can I cast it in some way to use it in an IN clause as integers like >UPDATE foo SET x = y WHERE id IN ( blah ); > or need I revert to dynamic SQL (EXECUTE...) ? > > Thanks, Axel > --- > [email protected] PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos > claudius > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- // Dmitriy.
Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause
Ooops, sorry UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]); 2010/10/29 Dmitriy Igrishin > Hey Axel, > > How about this solution: > UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; > ? > > 2010/10/25 Axel Rau > > Good morning, >> >> I have a function argument blah of type text containing something like >>33,44,55,66 >> . Can I cast it in some way to use it in an IN clause as integers like >>UPDATE foo SET x = y WHERE id IN ( blah ); >> or need I revert to dynamic SQL (EXECUTE...) ? >> >> Thanks, Axel >> --- >> [email protected] PGP-Key:29E99DD6 +49 151 2300 9283 computing @ >> chaos claudius >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> > > > > -- > // Dmitriy. > > > -- // Dmitriy.
[SQL] Re: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
>> rawi writes: > The Error was caused because I used UNION in place of UNION ALL. > > I still don't understand why the ARRAY (path) could not be grouped... Yeah, it's an unimplemented feature --- there's no hashing support for arrays. I hope to get that done for 8.5. In the meantime you have to use UNION ALL there. If you really need to eliminate duplicate rows, you can do that via DISTINCT in the outer query. I'm trying to do a similar sort of thing for a network containing ~9000 edges. I'm using a WITH RECURSIVE subquery, an array to track visited edges and a test to see whether the id of the 'current' edge is already in that array, as per the examples in the PostgreSQL 9.0 docs. Initially my main query seemed to run indefinitely so I introduced a LIMIT. I then found that as the LIMIT was increased the number of non-distinct edges returned by the query grew at a far greater rate than the number of distinct edges (with LIMIT 5 the number of distinct edges returned is only 628). Am I right in thinking that until arrays can be hashed that this issue could well limit the size of the networks that I can analyse, given a particular hardware config? Can anyone think of a way to use a temporary table rather than an array to store visited entities during a graph traversal as a means for overcoming this problem? FYI I've been running the following query as a test on PostgreSQL 9.0. WITH RECURSIVE upstream_pipes( downstream_end, upstream_end, name, depth, path, cycle) AS ( SELECT p.down_node, p.up_node, p.name, 1, ARRAY[p.name], False FROM pipes_table AS p WHERE p.downstream_end = '61ESI5R0WC' UNION ALL SELECT p.downstream_end, p.upstream_end, p.name, roi.depth + 1, path || p.name, p.name = ANY(path) FROM pipes_table AS p, upstream_pipes AS usp WHERE p.downstream_end = usp.upstream_end AND NOT cycle AND ( NOT ( p.end_1_impassible AND p.node_1 = p.downstream_end ) ) AND ( NOT ( p.end_2_impassible AND p.node_2 = p.downstream_end ) ) ), q as ( SELECT name FROM build_upstream_roi limit 2 ) SELECT COUNT(DISTINCT name), COUNT(name) FROM q Cheers, Will -- View this message in context: http://postgresql.1045698.n5.nabble.com/WITH-RECURSIVE-ARRAY-id-All-column-datatypes-must-be-hashable-tp2154712p3242676.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
