CODEWITHSUNDEEP
mysql
Alberto acepsut
Alberto acepsut

Reputation: 2022

Duplicate record in mySQL

I have a mySQL db with duplicate records, as from the attached image.

enter image description here

I am asking for a query to delete all duplicate records based on date + time, for all tables (foreachtables) in db

Thanks

Upvotes: 0

Views: 180

Answers (5)

Wojciech Jarzęcki
Wojciech Jarzęcki

Reputation: 1

Export NULL if table have autoincrement an for source use alias name, example :

INSERT INTO product
SELECT NULL,p.product_sku, 
                    p.product_s_desc, 
                    p.product_desc, 
                    p.product_thumb_image, 
                    p.product_full_image, 
                    p.product_tech_data, 
                    p.product_publish, 
                    p.product_weight, 
                    p.product_weight_uom, 
                    p.product_length, 
                    p.product_width, 
                    p.product_height, 
                    p.product_lwh_uom, 
                    p.product_url, 
                    p.product_in_stock, 
                    p.product_available_date, 
                    p.product_special, 
                    p.create_date, 
                    p.modify_date, 
                    p.product_name, 
                    p.attribute
FROM product AS p WHERE p.product_id=xxx;

Upvotes: 0

Leandro Bardelli
Leandro Bardelli

Reputation: 11578

As far I could see, you dont have autoincrement primary key or foreign key. If you dont have tables with foreign key or relation between, first you can list all your tables. After that, you can create a temporal "mirror" of one table (for eg, autogrill). Then you can do a:

INSERT INTO TemporalTable
SELECT DISTINCT

or a

INSERT INTO TemporalTable
SELECT Id, Date, Time FROM autogrill GROUP BY Id, Date, Time HAVING COUNT(*) > 1

.

TRUNCATE or DELETE FROM

without where and then put again your data with

INSERT INTO autogrill
SELECT * FROM TemporalTable

BE AWARE if you have primary keys doing this.

Upvotes: 1

John Woo
John Woo

Reputation: 263683

How about you create and STORED PROCEDURE for this?

 DELIMITER $$

 CREATE PROCEDURE `DeleteDup`()

     BEGIN
         -- Drops the table.
         DROP TABLE bad_temp;

         -- Creates a temporary table for distincts record.
         CREATE TABLE bad_temp(id INT, name VARCHAR(20));

         -- Selects distinct record and inserts it on the temp table
         INSERT INTO bad_temp(id,name) SELECT DISTINCT id,name FROM bad_table;

         -- Delete All Entries from the table which contains duplicate
         -- (you can add also condition on this)
         DELETE FROM bad_table;

         -- Selects all records from temp table and 
         -- inserts back in the orginal table
         INSERT INTO bad_table(id,name) SELECT id,name FROM bad_temp;

         -- Drops temporary table.
         DROP TABLE bad_temp;
     END$$

 DELIMITER ;

Please change tablename and column name to your desired schema.

so when you finish creating your STORED PROCEDURE, you can use it like this:

CALL DeleteDup();

Upvotes: 1

nolt2232
nolt2232

Reputation: 2644

One easy way to do this is to copy all the distinct records into a new table or an export. Then delete all records in the original table and copy them back in.

Upvotes: 0

BMN
BMN

Reputation: 8508

You can export your table using this request :

SELECT * FROM autogrill GROUP BY id

Then, empty your table, and import the export you made before. I don't know another easy way to erase duplicate entries using only a single request.

Upvotes: 0

Related Questions