CODEWITHSUNDEEP
mysqlsql
ArrayOutOfBound
ArrayOutOfBound

Reputation: 2638

Updatting a table by running query to prevent repeated records

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

1    M24
2    M22
3    M25
6    M35
7    M15
8    M18
12   M17
13   M19
14   M29
16   M12
18   M13

Upvotes: 1

Views: 86

Answers (4)

Imre L
Imre L

Reputation: 6249

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

Upvotes: 2

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

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

Upvotes: 0

Just_another_developer
Just_another_developer

Reputation: 5957

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);

Upvotes: 1

GarethD
GarethD

Reputation: 69789

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);

Upvotes: 2

Related Questions