{"id":264065,"date":"2024-05-27T09:31:00","date_gmt":"2024-05-27T09:31:00","guid":{"rendered":"https:\/\/imarticus.org\/blog\/?p=264065"},"modified":"2026-03-03T22:15:56","modified_gmt":"2026-03-03T16:45:56","slug":"how-to-delete-duplicate-rows-in-sql-detailed-guide","status":"publish","type":"post","link":"https:\/\/imarticus.org\/blog\/how-to-delete-duplicate-rows-in-sql-detailed-guide\/","title":{"rendered":"How to Delete Duplicate Rows in SQL? Detailed Guide With Syntax And Examples"},"content":{"rendered":"\r\n

Keeping data right is very important for all databases. When we have copies, it can cause problems and use more space. To help with this, we will learn how to delete duplicate rows in SQL. We’ll start with simple ways and work up to complicated ones.\u00a0<\/p>\r\n\r\n\r\n\r\n

We’ll explore a range of techniques, from the fundamental DISTINCT keyword to utilizing advanced Common Table Expressions<\/a><\/strong> (CTEs) in conjunction with the ROW_NUMBER() function. This will make you adapt at using SQL and keep your data clean and efficient in no time!\u00a0<\/p>\r\n\r\n\r\n\r\n

Delete duplicate rows in SQL<\/strong><\/h2>\r\n\r\n\r\n\r\n

In SQL, deleting duplicate rows means putting off entries from a table comprising equal information primarily based on specific criteria. Duplicate rows can occur for diverse reasons, including data entry mistakes, integrations from different assets, or incomplete deduplication methods.\u00a0<\/p>\r\n\r\n\r\n\r\n

Deleting duplicates facilitates:<\/p>\r\n\r\n\r\n\r\n

    \r\n
  • Improve data integrity<\/strong>: By eliminating redundant data, you make sure that the tables are correctly filled with data and constant.<\/li>\r\n\r\n\r\n\r\n
  • Save storage space<\/strong>: Duplicate rows occupy needless garage space, and getting rid of them can optimize database performance.<\/li>\r\n\r\n\r\n\r\n
  • Enhance data analysis<\/strong>: Duplicate rows can skew statistics evaluation consequences. Removing them results in more correct and dependable insights.<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n

    How to delete duplicate rows in SQL using sample data<\/h2>\r\n\r\n\r\n\r\n

    Here’s how testing makes it clear to see how duplicate rows take-out works in SQL:<\/p>\r\n\r\n\r\n\r\n

    Sample data<\/h3>\r\n\r\n\r\n\r\n

    Let’s consider a table named Customers with the following columns:<\/p>\r\n\r\n\r\n\r\n

    \r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
    CustomerID<\/strong><\/td>\r\nName<\/strong><\/td>\r\nEmail<\/strong><\/td>\r\n<\/tr>\r\n
    1<\/td>\r\nJohn Doe<\/td>\r\njohn.doe@email.com<\/td>\r\n<\/tr>\r\n
    2<\/td>\r\nJane Smith<\/td>\r\njane.smith@email.com<\/td>\r\n<\/tr>\r\n
    3<\/td>\r\nMike Jones<\/td>\r\nmike.jones@email.com<\/td>\r\n<\/tr>\r\n
    4<\/td>\r\nJohn Doe<\/td>\r\njohn.doe@email.com (duplicate)<\/td>\r\n<\/tr>\r\n<\/tbody>\r\n<\/table>\r\n<\/figure>\r\n\r\n\r\n\r\n

    This table has the same row twice for John Doe. We can take an example like this to show how various SQL ways find and delete duplicate rows.\u00a0<\/p>\r\n\r\n\r\n\r\n

    Delete duplicate rows in SQL using Group<\/strong><\/h2>\r\n\r\n\r\n\r\n

    Using GROUP BY and HAVING clauses is a strong method to remove repeated rows in SQL. You select columns to group the data and then use the HAVING clause to filter the groups. It helps find rows with the same values in specific columns.<\/p>\r\n\r\n\r\n\r\n

    Here’s how it works:<\/p>\r\n\r\n\r\n\r\n

      \r\n
    • Group By<\/strong>: You choose which columns to group the data by. This puts rows with the same values in those columns into categories.<\/li>\r\n\r\n\r\n\r\n
    • HAVING Clause<\/strong>: This filters the groups made by GROUP BY. You can use COUNT(*) inside HAVING to find groups with more than one row (copies).<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n

      How to delete duplicate rows in SQL with Group By and Having<\/strong><\/h2>\r\n\r\n\r\n\r\n

      To do this, follow the steps mentioned here.<\/p>\r\n\r\n\r\n\r\n

      Step 1. Find duplicate rules<\/strong>: Decide which columns show a duplicate in your data. For example, in a list of customers, duplicates can be found by matching Name and Email together.<\/p>\r\n\r\n\r\n\r\n

      Step 2. Build the DELETE Query<\/strong>: This is the basic format:<\/p>\r\n\r\n\r\n\r\n

      DELETE FROM your_table_name<\/strong><\/p>\r\n\r\n\r\n\r\n

      WHERE your_table_name.column_name_1 IN (\r\n\r\n\u00a0\u00a0SELECT column_name_1\r\n\r\n\u00a0\u00a0FROM your_table_name\r\n\r\n\u00a0\u00a0GROUP BY column_name_1, column_name_2 (columns for duplicate check)\r\n\r\n\u00a0\u00a0HAVING COUNT(*) > 1\r\n\r\n);<\/code><\/pre>\r\n\r\n\r\n\r\n
      Example<\/h3>\r\n\r\n\r\n\r\n
      Consider a table named Products with columns ProductCode, ProductName, and Price. We want to delete duplicate products based on ProductCode and Price.<\/p>\r\n\r\n\r\n\r\n
      DELETE FROM Products<\/strong><\/p>\r\n\r\n\r\n\r\n
      WHERE Products.ProductCode IN (\r\n\r\n\u00a0\u00a0SELECT ProductCode\r\n\r\n\u00a0\u00a0FROM Products\r\n\r\n\u00a0\u00a0GROUP BY ProductCode, Price\r\n\r\n\u00a0\u00a0HAVING COUNT(*) > 1\r\n\r\n);<\/code><\/pre>\r\n\r\n\r\n\r\n
      Result<\/strong>: This query will put things together by ProductCode and Price. The part saying HAVING COUNT(*) > 1 shows sets with the same products and prices. The DELETE statement then takes away rows with codes that are the same as these found duplicates.<\/p>\r\n\r\n\r\n\r\n
      Fetching and Identifying the duplicate rows<\/h2>\r\n\r\n\r\n\r\n
      It’s crucial to identify them accurately before knowing how to remove duplicates in SQL. Data science professionals often use SQL’s functionalities like querying and filtering to pinpoint these duplicate entries. Here are some methods to fetch and identify duplicate rows:<\/p>\r\n\r\n\r\n\r\n
      1. Using GROUP BY and COUNT(*)<\/h3>\r\n\r\n\r\n\r\n
      This is a common approach that uses both grouping and aggregate functions. The idea is to group rows based on the columns that define duplicates.\u00a0<\/p>\r\n\r\n\r\n\r\n
      Use COUNT(*) to determine the number of rows in each group. Groups with a count greater than 1 indicate duplicates.<\/p>\r\n\r\n\r\n\r\n
      Syntax<\/h3>\r\n\r\n\r\n\r\n
      SELECT column_name_1, column_name_2, …, COUNT(*) AS row_count<\/strong><\/p>\r\n\r\n\r\n\r\n
      FROM your_table_name<\/strong><\/p>\r\n\r\n\r\n\r\n
      GROUP BY column_name_1, column_name_2, …;<\/strong><\/p>\r\n\r\n\r\n\r\n
      2. Using DISTINCT and Self-Join<\/h3>\r\n\r\n\r\n\r\n
      The SQL remove duplicates option is a very handy way to handle your data. This method utilizes DISTINCT to fetch unique combinations and a self-join to compare rows. Use SELECT DISTINCT on the columns defining duplicates to get unique combinations. Later on, perform a self-join on the table itself, matching these unique combinations with the original table.<\/p>\r\n\r\n\r\n\r\n
      Syntax<\/h3>\r\n\r\n\r\n\r\n
      SELECT t1*.<\/strong><\/p>\r\n\r\n\r\n\r\n
      FROM (SELECT DISTINCT column_name_1, column_name_2, … FROM your_table_name) AS unique_data<\/strong><\/p>\r\n\r\n\r\n\r\n
      INNER JOIN your_table_name AS t1 ON (unique_data.column_name_1 = t1.column_name_1 AND …)<\/strong><\/p>\r\n\r\n\r\n\r\n
      WHERE unique_data.column_name_1 = t1.column_name_1 AND …;<\/strong><\/p>\r\n\r\n\r\n\r\n
      3. Using ROW_NUMBER()<\/h3>\r\n\r\n\r\n\r\n
      This method assigns a row number within groups defined by duplicate criteria, allowing you to identify duplicates based on their order.<\/p>\r\n\r\n\r\n\r\n
      Syntax<\/h4>\r\n\r\n\r\n\r\n
      SELECT *, ROW_NUMBER() OVER (PARTITION BY column_name_1, column_name_2, … ORDER BY column_name_3) AS row_num<\/strong><\/p>\r\n\r\n\r\n\r\n
      FROM your_table_name;<\/strong><\/p>\r\n\r\n\r\n\r\n
      How do you choose the right method?<\/h2>\r\n\r\n\r\n\r\n
      The right way depends on your needs and table size. Using GROUP BY and COUNT(*) is good for most cases. If you know how to remove duplicates in SQL, you might as well learn when to use which method.<\/p>\r\n\r\n\r\n\r\n
      If you have complicated copies or need to filter based on order, you could try ROW_NUMBER(). If you want to see all the copies, using self-join can help.<\/p>\r\n\r\n\r\n\r\n
      Delete duplicate rows in SQL with an intermediate table<\/h2>\r\n\r\n\r\n\r\n
      The “Intermediate table” way is good for doing away with the same rows in SQL. You use another table to keep the different info, and then swap it with the first table. For example, in a table called Customers with CustomerID, Name, and Email, with the same data.<\/p>\r\n\r\n\r\n\r\n
      Steps<\/h3>\r\n\r\n\r\n\r\n
        \r\n
      1. Create Intermediate Table: CREATE TABLE Customers_Temp LIKE Customers;<\/li>\r\n\r\n\r\n\r\n
      2. Insert Distinct Rows: INSERT INTO Customers_Temp<\/li>\r\n\r\n\r\n\r\n
      3. SELECT DISTINCT CustomerID, Name, Email<\/li>\r\n\r\n\r\n\r\n
      4. FROM Customers;<\/li>\r\n\r\n\r\n\r\n
      5. (Optional) Drop Original Table: DROP TABLE Customers;<\/li>\r\n\r\n\r\n\r\n
      6. Rename Intermediate Table: ALTER TABLE Customers_Temp RENAME TO Customers;<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
        Deleting duplicate rows in SQL<\/strong><\/strong> using ROW_NUMBER() function<\/h2>\r\n\r\n\r\n\r\n
        The ROW_NUMBER() function is a handy tool for deleting duplicate rows within a database table. For a query to delete duplicate records in SQL, you have a convenient option in this function.<\/p>\r\n\r\n\r\n\r\n
        This function assigns a unique number to each row within a result set, based on a specified ordering. It uses the following syntax:<\/p>\r\n\r\n\r\n\r\n
        ROW_NUMBER() OVER (PARTITION BY <column_list> ORDER BY <column_list>) AS row_num<\/strong><\/p>\r\n\r\n\r\n\r\n
        where<\/p>\r\n\r\n\r\n\r\n