HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / SQL / SQL – Remove Duplicate Rows without Temporary Table

SQL – Remove Duplicate Rows without Temporary Table

We as a developer, often come across situations where we have to work on database related stuffs. Mostly it is done when client send you its data in form of excel sheets and you push that data to database tables after some excel manipulations. I have also done it many times.

A very common problem faced in this approach is that it might result in duplicate rows at times, because data sent is mostly from departments like HR and finance where people are not well aware of data normalization techniques [:-)].

I will use Employee table where column names are id, name, department and email.

Below are the SQL scripts for generating the test data.

Create schema TestDB;

CREATE TABLE EMPLOYEE
(
    ID INT,
    NAME Varchar(100),
    DEPARTMENT INT,
    EMAIL Varchar(100)
);

INSERT INTO EMPLOYEE VALUES (1,'Anish',101,'anish@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (2,'Lokesh',102,'lokesh@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (3,'Rakesh',103,'rakesh@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (4,'Yogesh',104,'yogesh@howtodoinjava.com');

--These are the duplicate rows

INSERT INTO EMPLOYEE VALUES (5,'Anish',101,'anish@howtodoinjava.com');
INSERT INTO EMPLOYEE VALUES (6,'Lokesh',102,'lokesh@howtodoinjava.com');

Solution:

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

Above sql query will delete rows where name field is duplicate and only those unique rows will be retained where name is unique and ID field is lowest. For example rows with ID 5 and 6 will be deleted and rows with 1 and 2 will be retained.

delete-duplicate-rows-in-mysql-6630200

If you want to retain rows with latest generated ID values, then reverse the condition in where clause to e1.id < e2.id like this:

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

If you want to compare multiple fields and add appropriate where clause.

Note: Please execute above (or modified) query first on test data always to make sure it is producing the expected output.

Happy Learning !!

Was this post helpful?

Let us know if you liked the post. That’s the only way we can improve.
TwitterFacebookLinkedInRedditPocket

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Hareesh

    December 26, 2016

    Hi Lokesh,

    can you tell me how works in below scenario.

    INSERT INTO EMPLOYEE VALUES (1,’Anish’,101,’anish@howtodoinjava.com’);
    INSERT INTO EMPLOYEE VALUES (2,’Lokesh’,102,’lokesh@howtodoinjava.com’);
    INSERT INTO EMPLOYEE VALUES (3,’Rakesh’,103,’rakesh@howtodoinjava.com’);
    INSERT INTO EMPLOYEE VALUES (4,’Yogesh’,104,’yogesh@howtodoinjava.com’);
    –These are the duplicate rows
    INSERT INTO EMPLOYEE VALUES (1,’Anish’,101,’anish@howtodoinjava.com’);
    INSERT INTO EMPLOYEE VALUES (2,’Lokesh’,102,’lokesh@howtodoinjava.com’);

    in duplicate rows id for Anish and Lokesh same. how will delete records.

    Thanks.

  2. Anvesh

    February 8, 2016

    Nice Article!
    It really helps to all people of database community.
    I have also prepared two small demo to find and delete all duplicate records from the table of MySQL.
    You can visit my articles here:
    https://www.dbrnd.com/2015/09/find-duplicate-records-in-mysql/
    https://www.dbrnd.com/2015/09/delete-all-duplicate-rows-in-mysql/

  3. Sanjay Verma

    February 2, 2016

    delete t1 from users t1, users t2 where t1.id<t2.id and t1.gender=t2.gender;

  4. chetan

    January 23, 2015

    Executes well for me .

  5. Ashok

    July 30, 2014

    Sir i need to get a pdf report for students with ireports ,from more than one table..if i pass new JRE EmptydataSourse() .the design is displaying,but with Connection Object only blank pdf is displaying ..i am using struts 1.x ,can u please give me any suggetions for this task.

    • Lokesh Gupta

      July 30, 2014

      A simple data source implementation that simulates a data source with a given number of virtual records inside. It is called empty data source because even though it has one or more records inside, all the report fields are null for all the virtual records of the data source.

      http://jasperreports.sourceforge.net/api/net/sf/jasperreports/engine/JREmptyDataSource.html

      Looks like it’s correct behavior. Not sure because i really have not much experience with Jasper reports.

  6. Mad-hab

    July 4, 2014

    How the query will change if there is not Id column in the table , rest all remain as mentioned?

    • Lokesh Gupta

      July 4, 2014

      In that case, what makes two rows duplicates? By what logic you will identify two duplicate rows? And if you identify such logic, then convert it to query.. I hope I make sense.

      • Victor

        June 17, 2015

        It makes sence, but not resolves a problem.
        I saggest another method: add unique index to the table with some fields to check the duplicates. For example,

        ALTER IGNORE TABLE `employee` ADD UNIQUE INDEX `name_email` (`name`,`email`);

  7. Bob

    April 24, 2014

    How long should your example above take when executed on a table with over 300,000 rows? I had to execute the following to even get your example to work on my table:

    SET SESSION SQL_BIG_SELECTS=1;

    Thanks!

    • Lokesh Gupta

      April 24, 2014

      Hey Bob, I have not executed above query on big tables yet so can’t comment on performance of it. I believe that if database table is large, then we should use temp table approach. And never forget to back up data first .. 🙂

  8. Rajendra Joshi

    February 2, 2013

    If we have database and table already created in mysql
    then how to get database connection in spring 3.2

    • Lokesh Gupta

      February 2, 2013

      you will need to configure ibatis or hibernate from spring context. google it for now. i am not good health to help you right now.

Comments are closed on this article!

Search Tutorials

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Sealed Classes and Interfaces