1

I need a query to delete all the duplicate records but just to keep one latest record based on valid_until date column. I tried with the below but it says An expression of non-boolean type specified in a context where a condition is expected, near ','.

DELETE FROM tableOne
WHERE (id, valid_until ) NOT IN (
  SELECT id, MAX(valid_until )
  FROM tableOne
  GROUP BY id
)
Steve
  • 2,963
  • 15
  • 61
  • 133
  • 1
    [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) – Thom A Jun 24 '23 at 09:13

2 Answers2

4

You can use an updatable CTE.

CREATE TABLE Employees (
  id INT,
  name VARCHAR(50),
  valid_until DATE
);

INSERT INTO Employees (id, name, valid_until) VALUES
  (1, 'John Doe', '2022-06-30'),
  (2, 'Jane Smith', '2023-01-15'),
  (3, 'Michael Johnson', '2021-12-31'),
  (1, 'John Doe', '2023-06-30'),
  (2, 'Jane Smith', '2024-03-22');

WITH CTE AS (
  SELECT
    id,
    valid_until,
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_until DESC) AS row_num
  FROM Employees
)
DELETE FROM CTE 
WHERE row_num > 1;

SQL Fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Shanu
  • 124
  • 4
0

You can use left join and a condition is null to match the ones that are not the latests :

delete t 
from tableone t
left join (
  select id, MAX(valid_until ) as max_valid_until
  from tableOne
  group by id
) as s on s.id = t.id and s.max_valid_until = t.valid_until
where s.id is null
SelVazi
  • 10,028
  • 2
  • 13
  • 29
  • Which one is faster compared to the first solution? – Steve Jun 24 '23 at 23:16
  • 1
    The performance of both methods depends on how sql server optimizes the queries. You will need to analyze and test the performance of your queries on your specific database to determine which method is more efficient in your use case. – SelVazi Jun 25 '23 at 00:25
  • 1
    @Steve The other answer with an updatable CTE would normally be faster, but might be worth testing. Switching this answer out for a similar `not exists` might be worth a try also. – Charlieface Jun 25 '23 at 00:57