I am hoping to delete all the duplicates in my large DB. I wrote a query which first filters about 200k records out of a million records and then SELECTs the records.

Now, this whole process takes about 10 MINS 30 SECS under a certain system load. Now, if I replaced the SELECT command with DELETE , will it take the same amount of time?

I cannot test it myself because I do not have DELETE privileges right at this moment.

My Queries are as follows:

1. For Select

WITH CTE AS(
    SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    AGE,
    ROW_NUMBER() OVER(
        PARTITION BY
            ID,
            FIRSTNAME,
            LASTNAME,
            AGE
            ORDER BY
                ID,
                FIRSTNAME,
                LASTNAME,
                AGE
    ) AS row_num
    FROM 
        NEWTABLE
    
)
SELECT * FROM CTE WHERE row_num > 1;

1. For Delete

WITH CTE AS(
    SELECT
    ID,
    FIRSTNAME,
    LASTNAME,
    AGE,
    ROW_NUMBER() OVER(
        PARTITION BY
            ID,
            FIRSTNAME,
            LASTNAME,
            AGE
            ORDER BY
                ID,
                FIRSTNAME,
                LASTNAME,
                AGE
    ) AS row_num
    FROM 
        NEWTABLE
    
)
DELETE FROM CTE WHERE row_num > 1;