DB2 Delete From Where Exists With Row Number Group By and Left Join Dependencies

D2_Delete_Command.sql

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
)