Write the query to remove the duplicates from a table without using a temporary table?
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 )
DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;
— Using CTE for deletion instead of temp table , because cte scope is limited to query only
WITH cte_1 AS (
SELECT emp_id, name , ROW_NUMBER() OVER (PARTITION BY emp_id, name ORDER BY emp_id, name) AS row_num
FROM Employee
)
DELETE FROM cte_1
WHERE row_num > 1;
DELETE FROM STUDENT
Where rollno=(Select rollno from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;)
-using delete join
Delete t1 from table t1
inner join table t2
where t1.name = t2.name
and t1.id > t2.id
— using row_number()
delete from table where id in
(select id from
(select id , name, row_number() over (partition by name order by name) as row_num from table) t
where row_num > 1) ;