0
469
Question Evgenii Ermolaev · Oct 23, 2021

Multiple DELETE statements in a single query in Cache Monitr

#Caché #SQL

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

Comments

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>
0
Robert Cemper  Oct 23, 2021 to Robert Cemper

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

0
Evgenii Ermolaev  Oct 23, 2021 to Robert Cemper

Then there would be 70 000 OR conditions.

0
Robert Cemper  Oct 23, 2021 to Evgenii Ermolaev

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 ?

0
Evgenii Ermolaev  Oct 23, 2021 to Robert Cemper

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.

0
Robert Cemper  Oct 23, 2021 to Evgenii Ermolaev

"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

0
Vitaliy Serdtsev · Nov 2, 2021

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

<FONT COLOR="#0000ff">DELETE </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">TableName </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000000">= 2</FONT>
GO
<FONT COLOR="#0000ff">DELETE </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">TableName </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000000">= 3</FONT>
GO
<FONT COLOR="#0000ff">DELETE </FONT><FONT COLOR="#000080">FROM </FONT><FONT COLOR="#008000">TableName </FONT><FONT COLOR="#000080">WHERE </FONT><FONT COLOR="#008000">ID </FONT><FONT COLOR="#000000">= 4</FONT>
GO
0
Evgenii Ermolaev  Nov 2, 2021 to Vitaliy Serdtsev

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

0