Remove duplicate rows in MySQL

mysql delete duplicate rows with same id
delete duplicate records in mysql except one
mysql remove duplicates from query results
mysql delete duplicate rows but keep one
how to delete duplicate rows with same ids in mysql
delete duplicate rows in mysql without id
delete all duplicate rows except for one in mysql
mysql select without duplicates

I have a table with the following fields:

id (Unique)
url (Unique)
title
company
site_id

Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP):

SELECT title, site_id, location, id, count( * ) 
FROM jobs
GROUP BY site_id, company, title, location
HAVING count( * ) >1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so:

ALTER IGNORE TABLE jobs
ADD UNIQUE INDEX idx_name (site_id, title, company);

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this...

Remove duplicate rows in MySQL, A really easy way to do this is to add a UNIQUE index on the 3 columns. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER  Therefore, to remove duplicate rows, you need to delete everything except the ones marked with 1. This is done by running a DELETE query with the row_number as the filter. To delete duplicate rows run: DELETE FROM [table_name] WHERE row_number > 1; In our example dates table, the command would be: DELETE FROM dates WHERE row_number > 1;

If you don't want to alter the column properties, then you can use the query below.

Since you have a column which has unique IDs (e.g., auto_increment columns), you can use it to remove the duplicates:

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND (`a`.`title` = `b`.`title` OR `a`.`title` IS NULL AND `b`.`title` IS NULL)
    AND (`a`.`company` = `b`.`company` OR `a`.`company` IS NULL AND `b`.`company` IS NULL)
    AND (`a`.`site_id` = `b`.`site_id` OR `a`.`site_id` IS NULL AND `b`.`site_id` IS NULL);

In MySQL, you can simplify it even more with the NULL-safe equal operator (aka "spaceship operator"):

DELETE `a`
FROM
    `jobs` AS `a`,
    `jobs` AS `b`
WHERE
    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`ID` < `b`.`ID`

    -- Any duplicates you want to check for
    AND `a`.`title` <=> `b`.`title`
    AND `a`.`company` <=> `b`.`company`
    AND `a`.`site_id` <=> `b`.`site_id`;

How to Remove Duplicate Rows in MySQL {3 Ways to Delete}, The group by clause can also be used to remove duplicates. The go to solution for removing duplicate rows from your result sets is to include the  B) Delete duplicate rows using an intermediate table. Create a new table with the structure the same as the original table that you want to delete duplicate rows. Insert distinct rows from the original table to the immediate table. Drop the original table and rename the immediate table to the

MySQL has restrictions about referring to the table you are deleting from. You can work around that with a temporary table, like:

create temporary table tmpTable (id int);

insert  tmpTable
        (id)
select  id
from    YourTable yt
where   exists
        (
        select  *
        from    YourTabe yt2
        where   yt2.title = yt.title
                and yt2.company = yt.company
                and yt2.site_id = yt.site_id
                and yt2.id > yt.id
        );

delete  
from    YourTable
where   ID in (select id from tmpTable);

From Kostanos' suggestion in the comments: The only slow query above is DELETE, for cases where you have a very large database. This query could be faster:

DELETE FROM YourTable USING YourTable, tmpTable WHERE YourTable.id=tmpTable.id

Eliminating Duplicate Rows from MySQL Result Sets, JENNIFER DAVIS appears in two records with the same key of 22, making those rows exact duplicates. Nick Walberg's name fields are  Deleting duplicates on MySQL tables is a common issue, that usually comes with specific needs. In case anyone is interested, here ( Remove duplicate rows in MySQL) I explain how to use a temporary table to delete MySQL duplicates in a reliable and fast way,

If the IGNORE statement won't work like in my case, you can use the below statement:

CREATE TABLE your_table_deduped like your_table;
INSERT your_table_deduped SELECT * FROM your_table GROUP BY index1_id, index2_id;
RENAME TABLE your_table TO your_table_with_dupes;
RENAME TABLE your_table_deduped TO your_table;
#OPTIONAL
ALTER TABLE `your_table` ADD UNIQUE `unique_index` (`index1_id`, `index2_id`);
#OPTIONAL
DROP TABLE your_table_with_dupes;

How to Delete Duplicate Rows with Different IDs in MySQL (Part 3), There is no any field combination which identifies the record uniqually. I see at least 2 different solutions. First solution: move unique records to a copy of table  In this article we will see how to eliminate duplicate rows in SQL Server, in MySQL and Oracle. How to delete duplicate rows in SQL Server. Suponiendo que no tienes valores nulos, agrupa las columnas exclusivas (por ejemplo, col_1, col_2, col_3) y seleccionas la columna identificadora (el ID) el MIN o MAX (por ejemplo, row_id) como la fila que se debe conservar.

How to delete duplicate records in MySQL, in a table without IDs , 1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ? SELECT * FROM db.table HAVING  RENAME TABLE [table w/ duplicates] TO [temporary table name] Create an identical table with the original table name which contained the duplicates. INSERT INTO [new table] SELECT DISTINCT * FROM [old table with duplicates] Delete the temporary tables.

MySql: check and remove duplicate rows, delete-duplicate-rows-in-mysql. 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: The easiest solution may just be set rowcount 1 delete from t1 where col1=1 and col2=1 as seen here – Zorgarath Apr 29 '15 at 16:23 15 This answer will only delete the rows that has duplicates in col1.

SQL, The issue was caused by some duplicate rows in a very large the MySQL table (​more than 1,5 million rows). The obvious solution of course is to remove  SQL delete duplicate Rows using Common Table Expressions (CTE) We can use Common Table Expressions commonly known as CTE to remove duplicate rows in SQL Server. It is available starting from SQL Server 2005. We use a SQL ROW_NUMBER function, and it adds a unique sequential row number for the row.

Removing duplicate entries from a large table in MySQL, I'd like to delete duplicate rows in a MYSQL table without creating a new table. Generally, how is that done? If yes, proceed to step 5 below. If no, you have duplicate keys, yet unique rows, and need to decide which rows to save. This will usually entail either discarding a row, or creating a new unique key value for this row. Take one of these two steps for each such duplicate PK in the holddups table. Delete the duplicate rows from the original table.

Comments
  • Quick question: do always want duplicate (title, company, site_id) to not exist? If so, I'd set up a constraint in the database to enforce title, company, and site_id to be unique. Which would mean you wouldn't need a cleanup process. And it only takes a single line of SQL.
  • Please refer this link of stackoverflow.It worked for me as a charm.
  • I can recommend this solution (posted in another thread): stackoverflow.com/a/4685232/195835
  • You can also check this answer
  • Interesting, but the assumptions the IGNORE clause makes for removing those duplicates is a concern that might not match needs. Incorrect values being truncated to the closest acceptable match sound good to you?
  • Just for the record if your using InnoDB then you may have an issue with it, there is a known bug about using ALTER IGNORE TABLE with InnoDB databases.
  • The aforementioned bug @DarkMantis referred to and it's solution.
  • For InnoDB tables execute the following query first: set session old_alter_table=1;
  • This is no longer support in 5.7.4, dev.mysql.com/doc/refman/5.7/en/alter-table.html
  • I went with this solution, because I think it is the solution where it's most clear what the code is doing.
  • This should really be the accepted answer, because (A) it's standard SQL and (B) shows clearly what is going on with no magic behind the scenes.
  • this solution is not working properly , i tried to make some duplicate records and it does something like (20 rows affected) but if you run it again it will show you (4 rows affected) and so on until you reach (0 rows affected) which is kinda suspicious and here is what works best for me , it's almost the same but it works in one run, I edited the solution
  • For anyone that was confused like me, the NULL comparison terms are needed because NULL does not equal NULL in MySQL. If the relevant columns are guaranteed to not be NULL, you can leave these terms out.
  • Yes, the accepted answer is no longer valid, since MYSQL 5.7 so this should really be the accepted answer as it's universal and does not require temporary table creation either.