Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Delete Duplicate Records

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Delete Duplicate Records

      The MySQL Delete Duplicate Records

      Duplicate records in a database, including MySQL, is a very common occurrence. A MySQL database stores data in the form of tables consisting of rows and columns. Now, a record is said to be duplicated when two or more rows in a database table have same values.
      This redundancy might occur due to various reasons
      • The row might be inserted twice.
      • When raw data is imported from external sources.
      • There might be a bug in the database application.
      Whatever might be reason, deleting such redundancy becomes important to increase the data accuracy with less errors, or to increase the efficiency of database performance.

      Find Duplicate Values

      Before removing duplicate records, we must find whether they exist in a table or not. This is possible using the following ways −
      • GROUP BY Clause
      • COUNT() Method

      Example

      Let us first create table named "CUSTOMERS" containing duplicate values 
      CREATE TABLE CUSTOMERS(
         ID int,
         NAME varchar(100)
      );
      Using the following INSERT query, insert few records into the "CUSTOMERS" table. Here, we have added "John" as duplicate record 3 times 
      INSERT INTO CUSTOMERS 
      VALUES (1,'John'), (2,'Johnson'), (3,'John'), (4,'John');
      The CUSTOMERS table obtained is as follows
      id
      name
      1
      John
      2
      Johnson
      3
      John
      4
      John
      Now, we are retrieving the record that is duplicated in the table using the COUNT() method and GROUP BY clause as shown in the following query 
      SELECT NAME, COUNT(NAME) FROM CUSTOMERS
      GROUP BY NAME HAVING COUNT(NAME) > 1;

      Output

      Following is the output obtained
      NAME
      COUNT(NAME)
      John
      3

      Delete Duplicate Records

      To delete duplicate records from a database table, we can use the DELETE command. However, this DELETE command can be used in two ways to remove duplicates from a table
      • Using DELETE... JOIN
      • Using ROW_NUMBER() Function

      Using DELETE... JOIN

      To use DELETE... JOIN command in order to remove duplicate records from a table, we perform inner join on itself. This is applicable for cases that are not completely identical.
      
      For instance, suppose there is a repetition of customer details in customer records, but the serial number keeps incrementing. Here, the record is duplicated even if the ID is not same.
      Example
      In the following query, we are using the CUSTOMERS table created previously to remove duplicate records using DELETE... JOIN command 
      DELETE t1 FROM CUSTOMERS t1
      INNER JOIN CUSTOMERS t2
      WHERE t1.id < t2.id AND t1.name = t2.name;
      Output
      Following is the output obtained 
      Query OK, 2 rows affected (0.01 sec)
      Verification
      We can verify whether the duplicate records have been removed or not using the following SELECT statement 
      SELECT * FROM CUSTOMERS;
      We can see in the table obtained that the query removed duplicates and leave distinct records in the table
      ID
      NAME
      2
      Johnson
      4
      John

      Using ROW_NUMBER() Function

      The ROW_NUMBER() Function in MySQL is used to assign a sequential number, starting from 1, to each row in a result-set obtained from a query.
      Using this function, MySQL allows you to detect the duplicate rows, which can be removed with the DELETE statement.
      Example
      Here, we are applying the ROW_NUMBER() function to the CUSTOMERS table having duplicate values in the 'NAME' column. We will assign row numbers within a partition based on the 'NAME' column using the following query 
      SELECT id, ROW_NUMBER()
      OVER (PARTITION BY name ORDER BY name) AS row_num
      FROM CUSTOMERS;
      Following is the output obtained
      id
      row_num
      1
      1
      3
      2
      4
      3
      2
      1
      Now, with the following statement, delete the duplicate rows (rows with a row number greater than 1) 
      DELETE FROM CUSTOMERS WHERE id IN(
         SELECT id FROM (SELECT id, ROW_NUMBER()
         OVER (PARTITION BY name ORDER BY name) AS row_num
         FROM CUSTOMERS) AS temp_table WHERE row_num>1
      );
      We get the output as shown below 
      Query OK, 2 rows affected (0.00 sec)
      To verify whether the duplicate records have been removed or not, use the following SELECT query 
      SELECT * FROM CUSTOMERS;
      The result produced is as follows
      ID
      NAME
      1
      John
      2
      Johnson

      Delete Duplicate Records Using Client Program

      We can also delete duplicate records using client program.

      Syntax

      PHPNodeJSJavaPython
      To delete duplicate records through a PHP program, we need to perform inner join with "DELETE" command using the mysqli function query() as follows 
      $sql = "DELETE t1 FROM DuplicateDeleteDemo t1 INNER JOIN DuplicateDeleteDemo t2 WHERE t1.id < t2.id AND t1.name = t2.name";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $db = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      //printf('Connected successfully.');
      //let's create a table
      $sql = "CREATE TABLE DuplicateDeleteDemo(ID int,NAME varchar(100))";
      if($mysqli->query($sql)){
          printf("DuplicateDeleteDemo table created successfully...!\n");
      }
      //now lets insert some duplicate records;
      $sql = "INSERT INTO DuplicateDeleteDemo VALUES(1,'John')";
      if($mysqli->query($sql)){
          printf("First record inserted successfully...!\n");
      }
      $sql = "INSERT INTO DuplicateDeleteDemo VALUES(2,'Johnson')";
      if($mysqli->query($sql)){
          printf("Second record inserted successfully...!\n");
      }
      $sql = "INSERT INTO DuplicateDeleteDemo VALUES(3,'John')";
      if($mysqli->query($sql)){
          printf("Third records inserted successfully...!\n");
      }
      $sql = "INSERT INTO DuplicateDeleteDemo VALUES(4,'John')";
      if($mysqli->query($sql)){
          printf("Fourth record inserted successfully...!\n");
      }
      //display the table records
      $sql = "SELECT * FROM DuplicateDeleteDemo";
      if($result = $mysqli->query($sql)){
          printf("Table records(before deleting): \n");
          while($row = mysqli_fetch_array($result)){
              printf("ID: %d, NAME %s", 
                  $row['ID'], 
                  $row['NAME']);
                  printf("\n");
          }
      }
      //now lets count duplicate records
      $sql = "SELECT NAME, COUNT(NAME) FROM DuplicateDeleteDemo GROUP BY NAME HAVING COUNT(NAME) > 1";
      if($result = $mysqli->query($sql)){
          printf("Duplicate records: \n");
          while($row = mysqli_fetch_array($result)){
              print_r($row);
          }
      }
      //lets delete dupliacte records
      $sql = "DELETE t1 FROM DuplicateDeleteDemo t1 INNER JOIN DuplicateDeleteDemo t2 WHERE t1.id < t2.id AND t1.name = t2.name";
      if($mysqli->query($sql)){
          printf("Duplicate records deleted successfully...!\n");
      }
      $sql = "SELECT ID, NAME FROM DuplicateDeleteDemo";
      if($result = $mysqli->query($sql)){
          printf("Table records after deleting: \n");
          while($row = mysqli_fetch_row($result)){
              print_r($row);
          }
      }
      if($mysqli->error){
          printf("Error message: ", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as shown below 
      DuplicateDeleteDemo table created successfully...!
      First record inserted successfully...!
      Second record inserted successfully...!
      Third records inserted successfully...!
      Fourth record inserted successfully...!
      Table records(before deleting):
      ID: 1, NAME John
      ID: 2, NAME Johnson
      ID: 3, NAME John
      ID: 4, NAME John
      Duplicate records:
      Array
      (
          [0] => John
          [NAME] => John
          [1] => 3
          [COUNT(NAME)] => 3
      )
      Duplicate records deleted successfully...!
      Table records after deleting:
      Array
      (
          [0] => 2
          [1] => Johnson
      )
      Array
      (
          [0] => 4
          [1] => John
      )