Remove duplicates from database table (keeping all unique record)

Hi @fme_can_do_it , that should work for Microsoft SQL Server. If you’re using anything else it might need to be adapted to that particular SQL flavor.

You can check the syntax by using this non-destructive query (i.e. nothing gets deleted, only selected):

WITH cte AS (
    SELECT 
        contact_id, -- modify as needed
        first_name, -- modify as needed 
        last_name,  -- modify as needed
        email,  -- modify as needed
        ROW_NUMBER() OVER (
            PARTITION BY 
                first_name,  -- modify as needed
                last_name,  -- modify as needed
                email -- modify as needed
            ORDER BY 
                first_name,  -- modify as needed
                last_name,  -- modify as needed
                email -- modify as needed
        ) row_num
     FROM 
        sales.contacts -- modify as needed
)
SELECT * FROM cte
WHERE row_num > 1;

Of course you’ll have to modify/add/remove the columns and also modify your table name, I’ve flagged those with the comment “--modify as needed”

If you’re happy with the selection of records, you can delete the returned records by simply modifying the second to last line into DELETE FROM cte as in the example above. If you’re unsure, I recommend making a backup of the table first.

@david_r  i used Duplicate filter in my workflow and routed all duplicate features to the writer. In the configuration of this writer, I specified the delete operation based on the objectID column, which serves as a unique identifier. It worked! thank you!