-1

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.CAMERA < y.CAMERA);
waffles1313
  • 21
  • 3

4 Answers4

1
CREATE TABLE Table12
    ([ANALYSIS_ID] int, [TIMESTAMP] int, [EMOTION] varchar(5))
;

INSERT INTO Table12
    ([ANALYSIS_ID], [TIMESTAMP], [EMOTION])
VALUES
    (1, 5, 'HAPPY'),
    (2, 10, 'SAD'),
    (3, 10, 'SAD'),
    (4, 15, 'HAPPY'),
    (5, 15, 'ANGRY')
;

with cte as (select *, row_number() over (partition by emotion order by [ANALYSIS_ID] ) as rn   from Table12) 
delete from cte
where rn>1
select * from Table12

output

ANALYSIS_ID TIMESTAMP   EMOTION
1              5         HAPPY
2              10         SAD
5              15        ANGRY
Chanukya
  • 5,833
  • 1
  • 22
  • 36
0

You have two questions:

  1. what is wrong with my code
  2. is there a better way to delete the duplicate column entries

For the second question, it's a dup.

For the first question, please refer https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017. (Press F1 on delete). Correct syntax is

delete y
from Table12 y 
where exists (
qxg
  • 6,955
  • 1
  • 28
  • 36
0

Generic SQL command as below. you can put you column name/ condition and table name.

DELETE T from ( SELECT ROW_NUMBER()over(partition by column1 order by column2)a,* FROM TABLENAME )T where a>1

Ritesh Roushan
  • 36
  • 6
0

delete from TBL_VIDEO y where y.CAMERA < (select y2.CAMERA from TBL_VIDEO y2 where y.TIMESTAMP = y2.TIMESTAMP );

upvan111
  • 1