Update several rows in SQL statement at Management Portal

Question

Question

Albert Forcadell · Nov 16, 2016

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.

Discussion (11)0

Log in or sign up to continue

Kyle Baxter · Nov 16, 2016

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).

0 0

Alexander Koblov · Nov 17, 2016

Albert, notice comma after last END in the query:

THEN 400 END, WHERE

0 0

score 0 · Answer 1 · 2016-11-16T06:55:46-05:00

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.

score 0 · Answer 2 · 2016-11-16T13:38:48-05:00

Albert Forcadell · Nov 16, 2016

my version doesn't support this option...
??? i don't find it. sorry.

0 0

score 0 · Answer 3 · 2016-11-16T13:38:09-05:00

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 ;)

score 1 · Answer 4 · 2016-11-16T08:30:01-05:00

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')

score 0 · Answer 5 · 2016-11-16T13:45:28-05:00

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.

score 0 · Answer 6 · 2016-11-16T14:42:55-05:00

Alexander Koblov · Nov 16, 2016

Albert, please provide error that you receive when you try to run that query

0 0

score 0 · Answer 7 · 2016-11-17T07:21:55-05:00

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.

score 0 · Answer 8 · 2016-11-17T07:22:30-05:00

Albert Forcadell · Nov 17, 2016

see last modified comment

0 0

score 0 · Answer 9 · 2016-11-17T07:24:02-05:00

Albert Forcadell · Nov 17, 2016

it seems ok. thanks for your soon attention. i am on checking at all.

0 0