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


S_M.pngI 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
LVL 1
LD16Asked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
He assumes from your screenshot that the wanted relation is that with a filled MSTTID column.

Your rules says in short: If multiple relations exist, keep only the "highest" one. This should work with out the above MSTTID  assumption:

WITH Ordered
AS ( SELECT * ,
            ROW_NUMBER() OVER ( PARTITION BY SPoTInternalID
                                ORDER BY cardinality DESC ) AS 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 Ordered
WHERE rnk > 1;

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
LD16Author Commented:
Thank you for this proposal.
I was wondering it this query cover the various  requirements?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Yes my bad. Updated Cardinality column.

Take backup of the table and use below. Also please let me know if you face any issues.

;WITH CTE AS
(
	select * , ROW_NUMBER() OVER (PARTITION BY SPoTInternalID ORDER BY Cardinality 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

0
 
LD16Author Commented:
Both queries work. Thank you again for your help.
0
 
Pawan KumarDatabase ExpertCommented:
welcome glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.