Write the query to remove the duplicates from a table without using a temporary table?

Question

Write query

in progress 0
TheDataMonk 4 years 4 Answers 1484 views Grand Master 0

Answers ( 4 )

  1. swap007
    0
    Reply

    DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

  2. Payal Chuhan
    1
    Reply

    — 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;

  3. Puja
    0
    Reply

    DELETE FROM STUDENT
    Where rollno=(Select rollno from Student
    Group by rollno
    Having count (rollno)>1
    Order by count (rollno) desc;)

  4. Sriparna
    2
    Reply

    -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) ;

Leave an answer

Browse
Browse