Remove Duplicates from a database with no primary key in Mysql

  •  We can create a customized Row_Number and Map it to a Partition in table as follows
 With cte as (SELECT id,studentName,streetAddress, ROW_NUMBER() OVER(PARTITION BY id,studentName,streetAddress order by id,studentName,streetAddress) AS row_num   
 FROM springjdbc.student)  
 select * from cte WHERE row_num>1;  
 Delete from cte WHERE row_num>1;

  • Where our table is as follows
 SELECT id, studentName, streetAddress  
 FROM springjdbc.student;
  • To use the above query in database abstraction layers in code we can use as follows.
 Delete from student where id in (Select ds.id from (SELECT id,studentName,streetAddress, ROW_NUMBER() OVER(PARTITION BY id,studentName,streetAddress order by id,studentName,streetAddress) AS row_num FROM springjdbc.student) ds where ds.row_num>1)

c

Comments

Post a Comment

Popular posts from this blog

Mongo DB

  How can we handle optimistic locking in Mango DB? In Mango DB, we have versions associated with each record of the table. If 3 threads are trying to update our record and one third updates it Remaining 2 will have older or stale versions. As soon as they try to update the table with old version, it throws an optimistic lock exception. Using retryable, we can fetch the data of latest version And update latest data.
Read more

General DB Questions

  What are Cursers? A curser holds multiple rows returned by SQL statement. There are two types of cursor Implicit cursor They are generated by database itself. These are created by user. Explicit cursor These are created by user. Cursor has following steps We first declare a cursor Then we open cursor Then we fetch values from the cursor Then we close the cursor Declare CID Customer.id : id%type Cname Customer.name : id%type Cursor C1 is select statement  Open C1 Beginning  Loop Open C1 Fetch C1 into cid,cname Exit when c1% not found DBMS_Output.put_line(cid||” “||cname) End Loop Close C1 End What is the difference between primary key and foreign key? Prime key uniquely identifies the records.Foreign is a field in the table that is primary key in another table. Primary key cannot accept null values, foreign key can accept multiple null values. Primary  key is a clustered Index and data in the table is physically organised Foreign key do not automatically create an i...
Read more

Query Questions

Image
Find the total count of employees under each Manager? select empname ,( select count (*) from employee where reporterid = e . empid ) from employee e select e1 . empid , e1 . empname , count (*) from employee e1 inner join employee e2 on e1 . empid = e2 . reporterid group by e1 . empid , e1 . empname ;
Read more