Cred Interview Question | Duplicate Records

Question

By Mistake, Duplicate records exist in a table, how can we delete the copy of a record?

in progress 1
Dhruv2301 4 years 4 Answers 1185 views Great Grand Master 0

Answers ( 4 )

  1. /* We can remove Duplicate rows using DELETE JOIN Statement*/
    Delete t1 from Table t1
    JOIN Table t2
    on t1.column_name =t2.column_name

    /* Column_name is the column in which duplicate values exists*/

  2. Assume a ll the table names and column names

    Query:

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

  3. To delete the duplicate rows from the table in SQL Server, you follow these steps:

    1. Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
    2. Use DELETE statement to remove the duplicate rows.

  4. WITH Dupe AS
    (SELECT employee_id, full_name, salary,
    ROW_NUMBER() OVER (PARTITION BY full_name, salary ORDER BY full_name) AS row_num
    FROM table_name
    )
    DELETE FROM Dupe
    WHERE row_num > 1;

    or
    WHERE row_num != 1;

Leave an answer

Browse
Browse