SQL for delete using between

Question

Question

Vinicius Silva · Mar 4, 2021

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

Log in or sign up to continue

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

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

score 0 · Answer 1 · 2021-03-04T15:22:54-05:00

Eduard Lebedyuk · Mar 4, 2021

Can you please provide an example?

And the error you're getting.

0 0

score 0 · Answer 2 · 2021-03-05T06:42:55-05:00

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'))

score 0 · Answer 3 · 2021-03-05T10:33:18-05:00

songsong bai · Mar 5, 2021

I also think it's table locking.

0 0