DELETE + SELECT

Question

Olá a todos, gostaria de uma ajuda.

Preciso deletar um select que fiz porém não estou conseguindo o resultado correto

O select é: 

DELETE FROM CAD_DEVT 
WHERE EXISTS (
SELECT  CPF_DEV, TEL_TEL, PERC_TEL, COD_TEL, COD_TIPO
FROM CAD_DEVT AS A (NOLOCK)
WHERE A.TEL_TEL IN (SELECT B.TEL_TEL FROM CAD_DEVT AS B (NOLOCK)
           WHERE A.CPF_DEV = B.CPF_DEV AND A.TEL_TEL = B.TEL_TEL AND A.DDD_TEL = B.DDD_TEL
GROUP BY TEL_TEL 
HAVING COUNT(TEL_TEL)>1)
AND A.COD_TEL NOT IN (SELECT XX.COD_TEL FROM CAD_DEVT AS XX (NOLOCK) 
  WHERE XX.CPF_DEV = A.CPF_DEV AND
  XX.TEL_TEL = A.TEL_TEL AND 
  XX.DDD_TEL = A.DDD_TEL AND
  XX.PERC_TEL = (SELECT MAX(X.PERC_TEL) FROM CAD_DEVT AS X (NOLOCK) WHERE X.CPF_DEV = A.CPF_DEV AND X.TEL_TEL = A.TEL_TEL AND X.DDD_TEL = A.DDD_TEL) AND
  XX.COD_TEL = (SELECT MIN(Z.COD_TEL) FROM CAD_DEVT  AS Z (NOLOCK) WHERE Z.CPF_DEV = A.CPF_DEV AND Z.TEL_TEL = A.TEL_TEL AND Z.DDD_TEL = A.DDD_TEL AND Z.PERC_TEL = XX.PERC_TEL) AND
  XX.COD_TIPO = (SELECT MIN(W.COD_TIPO) FROM CAD_DEVT AS W (NOLOCK) WHERE W.CPF_DEV = A.CPF_DEV AND W.TEL_TEL = A.TEL_TEL AND W.DDD_TEL = A.DDD_TEL AND W.PERC_TEL = XX.PERC_TEL AND W.COD_TEL = XX.COD_TEL) )
AND STATUS_TEL = 0
)

Estou usando um where exists mas não estou conseguindo mesmo assim. Ajuda?

   

Answer 1

Deleted

Answer 2

Boa tarde,

Mari111, não sei se entendi corretamente a sua questão, mas experimente dessa forma:

DELETE FROM CAD_DEVT 
--WHERE EXISTS (
--SELECT  CPF_DEV, TEL_TEL, PERC_TEL, COD_TEL, COD_TIPO
FROM CAD_DEVT AS A (NOLOCK)
WHERE A.TEL_TEL IN (SELECT B.TEL_TEL FROM CAD_DEVT AS B (NOLOCK)
           WHERE A.CPF_DEV = B.CPF_DEV AND A.TEL_TEL = B.TEL_TEL AND A.DDD_TEL = B.DDD_TEL
GROUP BY TEL_TEL 
HAVING COUNT(TEL_TEL)>1)
AND A.COD_TEL NOT IN (SELECT XX.COD_TEL FROM CAD_DEVT AS XX (NOLOCK) 
  WHERE XX.CPF_DEV = A.CPF_DEV AND
  XX.TEL_TEL = A.TEL_TEL AND 
  XX.DDD_TEL = A.DDD_TEL AND
  XX.PERC_TEL = (SELECT MAX(X.PERC_TEL) FROM CAD_DEVT AS X (NOLOCK) WHERE X.CPF_DEV = A.CPF_DEV AND X.TEL_TEL = A.TEL_TEL AND X.DDD_TEL = A.DDD_TEL) AND
  XX.COD_TEL = (SELECT MIN(Z.COD_TEL) FROM CAD_DEVT  AS Z (NOLOCK) WHERE Z.CPF_DEV = A.CPF_DEV AND Z.TEL_TEL = A.TEL_TEL AND Z.DDD_TEL = A.DDD_TEL AND Z.PERC_TEL = XX.PERC_TEL) AND
  XX.COD_TIPO = (SELECT MIN(W.COD_TIPO) FROM CAD_DEVT AS W (NOLOCK) WHERE W.CPF_DEV = A.CPF_DEV AND W.TEL_TEL = A.TEL_TEL AND W.DDD_TEL = A.DDD_TEL AND W.PERC_TEL = XX.PERC_TEL AND W.COD_TEL = XX.COD_TEL) )
AND STATUS_TEL = 0
--)

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br