Question
Evgenii Ermolaev · Oct 23

Multiple DELETE statements in a single query in Cache Monitr

I need to execute multiple DELETE statements in a single query like this

DELETE FROM TableName WHERE ID = 2;
DELETE FROM TableName WHERE ID = 3;
DELETE FROM TableName WHERE ID = 4;

However It does not work when there're more than 2 statements and gives me an error

Expected FROM found WHERE^DELETE FROM TableName WHERE

Using IN is not an option.

Product version: Caché 2018.1
00
1 0 9 174
Log in or sign up to continue

what about simply using OR in  your SQL: 

DELETE FROM TableName WHERE ID = 2
                         OR ID = 3
                         OR ID = 4
                         OR <whaever condition>
                         OR <whaever other condition>

BUT:   if you intend to have tablename1,  tablename2,  tablename3, ...
you better wrap it in a ClassMethod and  project it as Stored Procedure 

So put it in a Stored procedure and deposit the conditions in a table.
Could be a classical case to use indirection or eXecute 
Whether maintaining a table or 70000 different WHERE should be the same effort.

At that point: Why to use SQL DELETE at all ?

Because I know for a fact that is possible to have multiple statements within a single query, but Monitor does not like it right know and I need to figure out why, because Cache errors are as uninformative as it gets, so I am forced to ask here.

It's a one-time task and I can't be bothered to create new tables and procedures for it.

"know for a fact that is possible to have multiple statements within a single query"

I wasn't aware of such a feature over the decades.
So it might be wise to get in contact with your personal Sales Engineer or contact WRC

Does 'GO' have to be on a new line? Because I tried to place it on the same line as the query and recieved an error