asked on

SQL: delete multiples duplicate rows with different relations

Hello experts,

I have the following query which allows me to identify various SPoTIntnarlid which exist multiple times in different relations:

select * from mstt_schema.SPOT_MSTT_MATCH CARDE
where [SPoTInternalID] in (
select [spotinternalid]
from mstt_schema.SPOT_MSTT_MATCH CARD
group by [SPoTInternalID]
having count(distinct cardinality)  > 1
)
order by 3

Open in new window

I attached the result of the query in xls file.

I would like to build DELETE queries in order to cover the following requirements:
1a-If SPoT internal Id exist with 1-1 and 1-0 relation DELETE line to 1-0 relation
1b-If SPoT Internal Id exist with 1-N and 1-0 relation DELETE line related to 1-0 relation
1c-If SPoT internal Id exist with N-1 and1-0 relation DELETE line related to 1-0 relation

2a-If SPoT internal Id exist with 1-N and 1-1 relation DELETE line related to 1-1 relation
2b- If SPoT Internal Id exist with N-1 and 1-1 relation DELETE line related to 1-1 relation

If you have questions, please contact me.

Thank you very much for your help.

Regards,
S_M.xlsx

Pawan Kumar

Please take backup of the table and try below-

;WITH CTE AS
(
	select * , ROW_NUMBER() OVER (PARTITION BY SPoTInternalID ORDER BY MSTTID DESC) rnk
	from mstt_schema.SPOT_MSTT_MATCH CARDE
	where [SPoTInternalID] in ( select [spotinternalid]	from mstt_schema.SPOT_MSTT_MATCH CARD group by [SPoTInternalID]	having count(distinct cardinality)  > 1)
)
DELETE FROM CTE WHERE rnk > 1

Open in new window

Luis Diaz

ASKER

Thank you for this proposal.
I was wondering it this query cover the various requirements?

ASKER CERTIFIED SOLUTION