Clash Royale CLAN TAG#URR8PPP

SQL - Deleting duplicate columns only if another column matches [duplicate]

This question already has an answer here:

I have the following table (TBL_VIDEO) with duplicate column entries in "TIMESTAMP", and I want to remove them only if the "CAMERA" number matches.

BEFORE:

ANALYSIS_ID | TIMESTAMP | EMOTION | CAMERA
-------------------------------------------
1 | 5 | HAPPY | 1
2 | 10 | SAD | 1
3 | 10 | SAD | 1
4 | 5 | HAPPY | 2
5 | 15 | ANGRY | 2
6 | 15 | HAPPY | 2


AFTER:

ANALYSIS_ID | TIMESTAMP | EMOTION | CAMERA
-------------------------------------------
1 | 5 | HAPPY | 1
2 | 10 | SAD | 1
4 | 5 | HAPPY | 2
5 | 15 | ANGRY | 2


I have attempted this statement but the columns wouldn't delete accordingly. I appreciate all the help to produce a correct SQL statement. Thanks in advance!

delete y
from TBL_VIDEO y
where exists (select 1 from TBL_VIDEO y2 where y.TIMESTAMP = y2.TIMESTAMP and y2.ANALYSIS_ID < y.ANALYSIS_ID, y.CAMERA = y.CAMERA, y2.CAMERA = y2.CAMERA);


This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5 Answers
5

Other solution :

delete f1 from yourtable f1
where exists
(
select * from yourtable f2
where f2.TIMESTAMP=f1.TIMESTAMP and f2.CAMERA=f1.CAMERA and f1.ANALYSIS_ID>f2.ANALYSIS_ID
)


try this:

delete f2 from (
select row_number() over(partition by TIMESTAMP, CAMERA order by ANALYSIS_ID) rang
from yourtable f1
) f2 where f2.rang>1


delete f2

select *

rang

use row_number and find the duplicate and delete them

row_number

delete from
(select *,row_number() over(partition by TIMESTAMP,CAMERA order by ANALYSIS_ID) as rn from TBL_VIDEO
) t1 where rn>1


;WITH cte
AS
(
select ANALYSIS_ID,
ROW_NUMBER() over(partition by TIMESTAMP, CAMERA order by ANALYSIS_ID) rnk
)

DELETE FROM cte WHERE cte.rnk > 1


You can use subquery :

subquery

select v.*
from tbl_video v
where analysis_id = (select min(v1.analysis_id)
from tbl_video v1
where v1.timestamp = v.timestamp and
v1.camera = v.camera
);


However, analytical function with top (1) with ties clause also useful :

top (1) with ties

select top (1) with ties v.*
from tbl_video v
order by row_number() over (partition by v.timestamp, v.camera order by v.analysis_id);


So, your delete version would be :

delete

delete v
from tbl_video v
where analysis_id = (select min(v1.analysis_id)
from tbl_video v1
where v1.timestamp = v.timestamp and
v1.camera = v.camera
);