Question
Vinicius Silva · Mar 4, 2021

SQL for delete using between

Why when I use a SQL on the Cache, the condition between "the expression" and "the expression", not permite to me, delete the all ID of this data class?

#SQL #Caché
Product version: Caché 2018.1
20
1 0 6 94
Log in or sign up to continue

Replies

Eduard Lebedyuk · Mar 4, 2021

Can you please provide an example?

And the error you're getting.

00
Rodolfo Moreira · Mar 4, 2021

Example 1

DELETE FROM Cidades
WHERE ID IN (SELECT ID FROM Cidades 
WHERE Cidade = 'Cidade 1' AND Cidade = 'Cidade 2') 

Example 2

DELETE FROM Pessoas
WHERE ID IN (SELECT ID FROM Pessoas
WHERE Salario BETWEEN 1000 AND 3000) 
20
Accepted answer
Robert Cemper · Mar 5, 2021

example 1 can't work.
WHERE Cidade = 'Cidade 1' AND Cidade = 'Cidade 2'
it is 1 value or the other but not both (AND) at the same time

10
Accepted answer
Rodolfo Moreira · Mar 5, 2021

You're right, this example below corrects the logic from example 1

DELETE FROM Cidades WHERE ID IN (SELECT ID FROM Cidades WHERE Cidade IN ('São Paulo','Belo Horizonte'))
00
guoguo wang · Mar 5, 2021

It may be caused by a business operation locking the table. I hope you can find the relevant information and confirm this problem.

00
Accepted answer