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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ste5anSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Pawan KumarDatabase 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.