SQL - Deleting duplicate columns only if another column matches

Question

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);

Esperento57 · Answer 1 · 2018-08-13T09:49:01.563

2

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

edited Aug 13 '18 at 09:49

answered Aug 13 '18 at 08:37

Esperento57

16,521
3
39
45

1

+1 This will work. But just a small remark. Adding the fields in the sub-query is handy if you want to replace that `delete f2` by a `select *` to test what will be deleted. But those are actually not required for the delete. Just the `rang` alone would be enough. – LukStorms Aug 13 '18 at 09:44
exactly, i remove that ;) – Esperento57 Aug 13 '18 at 09:48

Esperento57 · Accepted Answer · 2018-08-13T09:20:30.507

1

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
)

edited Aug 13 '18 at 09:20

answered Aug 13 '18 at 08:40

Esperento57

16,521
3
39
45

This worked. Thank you! – waffles1313 Aug 13 '18 at 09:53
you'r welcome :) – Esperento57 Aug 13 '18 at 09:54

score 0 · Answer 3 · answered Aug 13 '18 at 08:47

0

use row_number and find the duplicate and delete them

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

answered Aug 13 '18 at 08:47

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 4 · answered Aug 13 '18 at 09:08

0

;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

answered Aug 13 '18 at 09:08

Deep patel

136
6

Yogesh Sharma · Answer 5 · 2018-08-13T09:51:57.910

You can use 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 :

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 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 
                    );

SQL - Deleting duplicate columns only if another column matches

5 Answers5