Update several rows in SQL statement at Management Portal
Since now i have been working from external connection but i want to work with the SQL utility of Management Portal
i dont know how to do with several instructions like in other editors like this example
update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo = 80 where CodigoNum = '100' and Empresa = 'CO' update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo = 400 where CodigoNum = '101' and Empresa = 'CO' update Prod.Articulos set Alto = 1646, Ancho = 16, Fondo = 400 where CodigoNum = '102' and Empresa = 'CO'
it returns
ERROR #5001: 25: SQL ERROR #25: Input (UPDATE) encountered after end of query^ UPDATE Prod . Articulos SET Alto = ? , Ancho = ? , Fondo = ? WHERE CodigoNum = ? AND Empresa = ? UPDATE action = (parse) rtn = () $ze = (<SYNTAX>errdone+2^%qaqqt) err = (qaqperr)
neither it works with a structure like this
UPDATE Prod.Articulos
SET Alto = CASE CodigoNum
WHEN '100' THEN 1646
WHEN '101' THEN 1646
END,
Ancho = CASE CodigoNum
WHEN '100' THEN 16
WHEN '101' THEN 16
END,
Fondo = CASE CodigoNum
WHEN '100' THEN 80
WHEN '101' THEN 400
END,
WHERE CodigoNum IN ('100','101')perhaps you find it an easy trouble but i have tried all the ways i could and it is impossible for me.
thanks in advance.
Comments
if you click on more the link just after max 1000 on the right you can select Dialect to MSSQL this allows me to run multi line sql statements.
my version doesn't support this option... ??? i don't find it. sorry.
When I want to run a bunch of statements I find it easier to open the SQL Shell and parameterize the queries. Like so:
SAMPLES>d $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>update sample.person set name=? where name=?
1. update sample.person set name=? where name=?
Enter the value for parameter '1': Kyle
Enter the value for parameter '2': Tester
executing statement with parameter values: set %tResult=%tStatement.%Execute("'Kyle'","'Test'")
1Rows Affected
statement prepare time(s)/globals/lines/disk: 0.1260s/4915/70580/0ms
execute time(s)/globals/lines/disk: 0.0033s/10/110/0ms
cached query class: %sqlcq.SAMPLES.cls14
---------------------------------------------------------------------------
SAMPLES>>#
1. update sample.person set name=? where name=?
SAMPLES>>#1
update sample.person set name=? where name=?
1. update sample.person set name=? where name=?
Enter the value for parameter '1': Sexy Ginger God
Enter the value for parameter '2': Kyle
executing statement with parameter values: set %tResult=%tStatement.%Execute("'Sexy Ginger God'","Kyle")
1 Rows Affected
statement prepare time(s)/globals/lines/disk: 0.0002s/5/98/0ms
execute time(s)/globals/lines/disk: 0.0002s/5/113/0ms
cached query class: %sqlcq.SAMPLES.cls14
---------------------------------------------------------------------------
SAMPLES>>
Some notes:
1) Note that entering the hash/pound/tic-tac-toe sign (#) gives you a list of statements that have been run
2) You can run these statements by following that sign with the number. So #1 runs the first statement from this session (it's actually saved by process)
3) Parameterized queries do not need quotes, and can be easily rerun
4) Not allowing multiple statements per line is a way to help us be more resilient against SQL Injection attacks (that said, parametrization is still key).
thank you very much.
i understand a little your way but too advanced to me and colleagues to apply in regular queries.
it remains as a final resource ;)
Albert,
you second option should work. I've just tested it on 2016x1.
Probably the reason of error is last comma between END and WHERE:
END,
WHERE CodigoNum IN ('100','101')
i ve tried with and without coma. also with simple statement
... and ERROR
UPDATE Prod.Articulos SET Alto = CASE CodigoNum WHEN '100' THEN 1646 END, Ancho = CASE CodigoNum WHEN '100' THEN 16 END, Fondo = CASE CodigoNum WHEN '100' THEN 80 END WHERE CodigoNum IN ('100') and empresa ='CO'
i am very interested in that option, that's really the only one i can apply at the moment i think, but with the right structure.
several rows - 3 fields to update - 2 'where' restrictions
please.
Albert, please provide error that you receive when you try to run that query
thanks a lot.
it seems that works. All in one line, with no carry changes.
UPDATE Prod.Articulos SET Alto = CASE CodigoNum WHEN '100' THEN 1646 WHEN '101' THEN 1646 END, Ancho = CASE CodigoNum WHEN '100' THEN 16 WHEN '101' THEN 16 END, Fondo = CASE CodigoNum WHEN '100' THEN 80 WHEN '101' THEN 80 END WHERE CodigoNum IN ('100','101') and empresa ='CO'
i try with more complete rows afected at real case.
Albert, notice comma after last END in the query:
THEN 400 END, WHERE
see last modified comment
it seems ok. thanks for your soon attention. i am on checking at all.