Cred Interview Question | Duplicate Records
Question
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
It will take less than 1 minute to register for lifetime. Bonus Tip - We don't send OTP to your email id Make Sure to use your own email id for free books and giveaways
Answers ( 4 )
/* 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*/
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;
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.
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;