New post
Question
Albert Forcadell · Nov 16, 2016

Update several rows in SQL statement at Management Portal

#Beginner #SQL #Caché

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 588
Discussion (11)0
Log in or sign up to continue
Mark Connelly · Nov 16, 2016

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.

0 0
Albert Forcadell · Nov 16, 2016

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

0 0
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
Albert Forcadell · Nov 16, 2016

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

0 0
Alexander Koblov · Nov 16, 2016

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')
1 0
Albert Forcadell · Nov 16, 2016

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.

0 0
Alexander Koblov · Nov 16, 2016

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

0 0
Albert Forcadell · Nov 17, 2016

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.

0 0
Alexander Koblov · Nov 17, 2016

Albert, notice comma after last END in the query:

THEN 400 END, WHERE
0 0
Albert Forcadell · Nov 17, 2016

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

0 0