SQL Server SQL Query to delete duplicate records but keep latest

vlf7wbxs 于 2023-06-28 发布在其他

关注(0)|答案(2)|浏览(134)

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
)

sql-server

来源：https://stackoverflow.com/questions/76545335/sql-query-to-delete-duplicate-records-but-keep-latest

2条答案

按热度按时间

fcy6dtqo1#

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

赞(0）回复(0）举报 2023-06-28

dwbf0jvd2#

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

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server SQL Query to delete duplicate records but keep latest

2条答案

相关问题

热门标签

最新问答