DECLARE @tabold TABLE
(
PAYEE_TVMEDIA_ID INT ,
TVMEDIA_ID INT ,
PayeeAccount_ID INT
)
DECLARE @taboldchuli TABLE
(
PAYEE_TVMEDIA_ID INT ,
TVMEDIA_ID INT ,
PayeeAccount_ID INT
)
INSERT @tabold
( PAYEE_TVMEDIA_ID ,
TVMEDIA_ID ,
PayeeAccount_ID
)
SELECT a.PAYEE_TVMEDIA_ID ,
a.PayeeAccount_ID ,
a.TVMEDIA_ID
FROM PAYEE_TVMEDIA a
INNER JOIN ( SELECT PayeeAccount_ID ,
TVMEDIA_ID
FROM PAYEE_TVMEDIA b
GROUP BY PayeeAccount_ID ,
TVMEDIA_ID
HAVING COUNT(*) > 1
) b ON a.PayeeAccount_ID = b.PayeeAccount_ID
AND a.TVMEDIA_ID = b.TVMEDIA_ID
ORDER BY a.PayeeAccount_ID ,
b.TVMEDIA_ID
INSERT INTO @taboldchuli
( PAYEE_TVMEDIA_ID ,
TVMEDIA_ID ,
PayeeAccount_ID
)
SELECT PAYEE_TVMEDIA_ID ,
TVMEDIA_ID ,
PayeeAccount_ID
FROM CAMSDB_NMS.dbo.PAYEE_TVMEDIA
DELETE FROM @taboldchuli
WHERE PAYEE_TVMEDIA_ID IN (
SELECT a.PAYEE_TVMEDIA_ID
FROM @tabold a
WHERE a.PAYEE_TVMEDIA_ID NOT IN ( SELECT MIN(b.PAYEE_TVMEDIA_ID)
FROM @tabold b
GROUP BY b.PayeeAccount_ID ,
b.TVMEDIA_ID ) )
SELECT *
FROM @taboldchuli
