Skip to content

Instantly share code, notes, and snippets.

@cwg999
Created May 29, 2019 19:24
Show Gist options
  • Save cwg999/a2c9a3d99d3de04ab99bfe379a997f1d to your computer and use it in GitHub Desktop.
Save cwg999/a2c9a3d99d3de04ab99bfe379a997f1d to your computer and use it in GitHub Desktop.
DB2 Delete From Where Exists With Row Number Group By and Left Join Dependencies
DELETE FROM VCINTER.ATSDATA FF
WHERE EXISTS(
SELECT 1 FROM ( SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY UPPER(C.PKOLD)) AS RN1, UPPER(C.PKOLD) as PKOLD2,E.PKOLDCNT, D.*,C.*
FROM VCINTER.ATSDATA C
LEFT JOIN (
SELECT
COUNT(RN) as CNT,PARPKEY
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PARPKEY) as RN, A.* FROM VCINTER.ATSDETAILS A) B
GROUP BY PARPKEY
) D ON C.PKEY = D.PARPKEY
LEFT JOIN (
SELECT
COUNT(RN) as PKOLDCNT,UPPER(PKOLD) as PKOLD
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY PKOLD) as RN, G.* FROM VCINTER.ATSDATA G) F
GROUP BY UPPER(PKOLD)
) E ON UPPER(C.PKOLD) = UPPER(E.PKOLD)
WHERE C.PKOLD IS NOT NULL
--LEFT JOIN VCINTER.ATSDETAILS B ON C.PKEY = B.PARPKEY
--WHERE UPPER(PKOLD) = '0023717C-FE15-4F6A-ADB2-B4E66EFB5779'
-- WHERE CNT IS NULL
AND PKOLDCNT > 1
--WHERE PKOLD2 <> E.PKOLD
ORDER BY PKOLD2,PKOLD,CNT
) GG
WHERE RN1 = 1
) HH
WHERE FF.PKOLD = HH.PKOLD
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment