New post
Question
Vinicius Silva · Mar 4, 2021

SQL for delete using between

#SQL #Caché

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?

Product version: Caché 2018.1
2
0 203
Discussion (6)1
Log in or sign up to continue
Eduard Lebedyuk · Mar 4, 2021

Can you please provide an example?

And the error you're getting.

0 0
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) 
2 0
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

1 0
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'))
0 0
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.

0 0
songsong bai · Mar 5, 2021

I also think it's table locking.

0 0