Question
Albert Forcadell · Nov 16, 2016

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.

0
0 584
Discussion (11)1
Log in or sign up to continue

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

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