Erase older duplicate data in MS SQL server

Question

Erase older duplicate data in MS SQL server

Navigation

#1 by (4 votes)

0

I have a problem in a table that has duplicate data. The duplicate data is identified by an ID, where each line has the identifier and a date, how can I delete all the data with more than 1 record from the table, leave only the most recent record?

The database is an MS SQL SERVER, that is, the script should cause you to delete the 4 oldest lines in the list below:

ID            DATA
17081618585 | 18.02.02 18:42:41
17081618585 | 18.02.02 19:30:41
17081618585 | 18.02.02 20:42:41
17081618585 | 18.02.02 20:42:41
17081618585 | 18.02.02 22:42:42

sql-server delete

asked by anonymous 04.02.2018 / 15:00

1 answer

OnClick event is not working! [duplicate] problems with date conversion with php

score 4 · Answer 1

A simple approach is to use a Common Table Expression ) to select the records that you NOT want to delete. After that, just do a DELETE with join with CTE created to delete the records.

See the code below:

with cte as (
    select id,max(data) max_data
    from #teste
    group by id
)
delete t
from #teste t
inner join cte
on  (t.id = cte.id)
and (t.data <> cte.max_data)

% w / w% select the maximum date by ID. After that, a DELETE is done on the table in question and the join specifies that the date to be deleted must be different from the date of the CTE.

A detail : In order for this to work the field that stores the date must necessarily be a date in SQL Server, that is, a field of type CTE or affine. If it is a CTE field then the datetime that is in varchar will fail.