Updatting a table by running query to prevent repeated records

Question

Avoiding repeated records in table

I have a Table like one below

CREATE TABLE models(model_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                         model_no varchar(255));                             

INSERT INTO models(model_no)
                 VALUES('M24'),
                       ('M22'),
                       ('M25'),
                       ('M24'),
                       ('M24'),              
                       ('M35'),
                       ('M15'),
                       ('M18'),
                       ('M25'),
                       ('M15'),
                       ('M15'),
                       ('M17'),
                       ('M19'),
                       ('M29'),
                       ('M29'),
                       ('M12'),
                       ('M12'),
                       ('M13'),
                       ('M29');

I want to remove the repeated model from this table by running a delete or update query so that the model will occur only once as below

possible duplicate : http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql — Suresh Kamrushi, Dec 31 '12 at 11:11
`delete from models dd where exists (select * from models xx where xx.model_no = dd.model_no AND xx.model_id < dd.model_id);` — wildplasser, Dec 31 '12 at 11:13
MySQL does not allow you to select from the table being deleted from so @Wildplasser's answer will not work, you need to use a temporary table to get around this (A demo of this is provided in the answer linked to in the first comment) — GarethD, Dec 31 '12 at 11:18
If I understand this correctly, you just want to avoid duplicity in `model_no` column? In that case I would recommend making `model_no` UNIQUE. Actually I don't quite understand why you would want to INSERT and immediately DELETE some values... — davak, Dec 31 '12 at 11:23
You could tuck it away in a subquery, IIRC. But effectivel,y you are using a hammer to do drive a screw. — wildplasser, Dec 31 '12 at 11:24
@davak I am not interested in insert and deleting immediately.The table already have some 10k records with duplicate entries — ArrayOutOfBound, Dec 31 '12 at 11:26
in that case I would still alter the table to avoid future problems with duplicate entries. You can `ALTER IGNORE TABLE models ADD UNIQUE INDEX idx_model (model_no);` that will drop the duplicates and won't allow to create new duplicates — davak, Dec 31 '12 at 11:33
You should screen your data so that there are no duplicates in the VALUES list. If some of those values already exist in the target table, then you have a different problem, but having a unique index on the target table to enforce uniqueness is an important first step. — Jonathan Leffler, Jan 01 '13 at 13:10
possible duplicate of [How do I delete all the duplicate records in a MySQL table without temp tables](http://stackoverflow.com/questions/14046355/how-do-i-delete-all-the-duplicate-records-in-a-mysql-table-without-temp-tables) — Lev Levitsky, Jan 01 '13 at 16:26

score 2 · Answer 1 · answered Dec 31 '12 at 11:30

To remove duplicates that already exist in the table you can use:

CREATE TEMPORARY TABLE tmp (ModelID INT NOT NULL);

INSERT tmp (ModelID)
SELECT  t1.Model_ID
FROM    Models t1
WHERE   EXISTS
        (   SELECT  1
            FROM    Models t2
            WHERE   t1.Model_No = t2.Model_No
            AND     t2.Model_ID < t1.Model_ID
        );

DELETE  
FROM    Models
WHERE   Model_ID IN (SELECT ModelID FROM tmp);

SQL Fiddle

Then going forward you should add a Unique index to stop further duplicates.

CREATE UNIQUE INDEX UQ_Models ON Models (Model_No);

score 2 · Accepted Answer · answered Dec 31 '12 at 11:42

2

delete m from models m
join models m2 on m2.model_no = m.model_no and m.model_id > m2.model_id

answered Dec 31 '12 at 11:42

Imre L

6,159
24
32

score 1 · Answer 3 · answered Dec 31 '12 at 11:31

Try using temporary table.

create temporary table tmpTable (model_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                     model_no varchar(255));



 insert  tmpTable
    (model_id)
 select  model_id
 from    models m
 where   exists
    (
    select  *
    from   models m2
    where   m2.model_no = m.model_no

            and m2.model_id> m.model_id
    );

delete  
from    models
where   model_id in (select model_id from tmpTable);

score 0 · Answer 4 · answered Dec 31 '12 at 11:33

0

Try this:

ALTER IGNORE TABLE `models`   
ADD UNIQUE INDEX (`model_no`);

answered Dec 31 '12 at 11:33

Saharsh Shah

28,687
8
48
83

Updatting a table by running query to prevent repeated records

4 Answers4