Written by

Question Vinicius Silva · Mar 4, 2021

SQL for delete using between

#Caché #SQL

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

Discussion (6)0

Add reply

Comments

Eduard Lebedyuk · Mar 4, 2021

Can you please provide an example?

And the error you're getting.

0 0

Rodolfo Moreira dos Santos · 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)

0 0

Robert Cemper Mar 5, 2021 to Rodolfo Moreira dos Santos

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

0 0

Rodolfo Moreira dos Santos Mar 5, 2021 to Robert Cemper

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 to guoguo wang

I also think it's table locking.

0 0