Multiple DELETE statements in a single query in Cache Monitr

Question

Question

Evgenii Ermolaev · Oct 23, 2021

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

Discussion (9)0

Log in or sign up to continue

Robert Cemper · Oct 23, 2021

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>

1 0

Vitaliy Serdtsev · Nov 2, 2021

It should be like this (see Work with multiple sql statements):

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

0 0

score 0 · Answer 1 · 2021-10-23T03:16:00-04:00

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

score 0 · Answer 2 · 2021-10-23T06:13:41-04:00

Evgenii Ermolaev · Oct 23, 2021

Then there would be 70 000 OR conditions.

0 0

score 0 · Answer 3 · 2021-10-23T06:22:47-04:00

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 ?

score 0 · Answer 4 · 2021-10-23T06:28:35-04:00

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.

score 0 · Answer 5 · 2021-10-23T06:35:00-04:00

"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

score 0 · Answer 6 · 2021-11-02T21:06:48-04:00

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

score 0 · Answer 7 · 2021-11-03T02:09:49-04:00

Vitaliy Serdtsev · Nov 3, 2021

Yes

0 0