CODEWITHSUNDEEP
sql-server
matt
matt

Reputation: 11

Duplicate Id not the row id

When I upload my table project in my SQL Server, I see appear duplicate of the row such the example below using Microsoft SQL Server Management Studio:

ID      Status          Code
----------------------------------
144702  Completed       Ok-Q8 AB
144702  Completed       Ok-Q8 AB

I try to run the delete command but I can't make it work.. my goal it is to scan all the table ID and see if there are a row with same ID and then delete one of them so, in the end, will be just 1 row with that ID

delete from project
where ID not in
(
    select min(ID)
    from project
);

My goal it is to scan all the table ID and see if there are a row with same ID and then delete one of them so, in the end, will be just 1 row with that ID

Upvotes: 0

Views: 48

Answers (1)

Standin.Wolf
Standin.Wolf

Reputation: 1234

Try this

WITH cte AS (
    SELECT 
        ID,
        Status,
        Code,
        ROW_NUMBER() OVER (
            PARTITION BY 
        ID,
        Status,
        Code,
            ORDER BY 
        ID,
        Status,
        Code,
        ) row_num
     FROM 
        project
)
DELETE FROM cte
WHERE row_num > 1;

Upvotes: 3

Related Questions