remove duplicate data from sql

问题:

I have a sql database named "data" and a table "disk", where there are 5 columns

CREATE TABLE disk ( id int(11) NOT NULL, title text COLLATE utf8_unicode_ci NOT NULL, link text COLLATE utf8_unicode_ci NOT NULL, mag text COLLATE utf8_unicode_ci NOT NULL, size varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

the "mag" column has a some of the duplicates.

and I want to delete the complete row where mag column is same.

note:- let say mag column has 1,2,3,4,4,5.... I want to delete a single 4 from it which is duplicate. means I don't want to completely delete both the 4. one "4" must be kept.

tell the SQL query which can do that.... before marking this question as a duplicate, I must tell you that I have already seen the similar question but none of them worked. for more info just comment.

回答1:

You can do the following..
Creating new table and keeping random row :

1. first copy table disk(unique data) to temp table disk2.

2. drop table disk.

3. rename temp table disk2 to disk.

   create table disk2 select * from disk group by d;
   
   drop table disk;
   
   rename table disk2 to disk;
   

NOTE : Here we using group by with * because OP does not care which row to keep.

Creating new table and keeping row with min or max id : Another way to do this while keeping row with min or max id

/*copy data from disk to temp table disk2*/
create table disk2 select * from disk
    where id in (select min(id) from disk group by d);
/*drop table disk*/
drop table disk;
/*rename temp table to disk*/
rename table disk2 to disk;

UPDATE: Another way to do this
Deleting duplicates from existing table

    /*first create a dups table for duplicates*/
    create table dups select * from disk
        where id not in (select min(id) from disk group by d);
    /*now delete all rows which are present in dups table*/
    delete from disk where id in (select id from dups);
    /*now delete the dups table*/
    drop table dups;

回答2:

try this below to delete duplicate with same d column and keep one row with lowest id value: :

DELETE d1 
FROM  disk d1, disk d2 
WHERE d1.id > d2.id AND 
      d1.d = d2.d;

回答3:

Try this:

DELETE disk
FROM disk
INNER JOIN (
       SELECT id,
               d,
               CASE WHEN d = @prevd 
                       THEN @id:=@id+1
                    ELSE @id:=1
               END AS rankNum,
               @prevd:=d AS prd
       FROM disk, (SELECT @prevd:=NULL,@id:=NULL) t
      ) t1
ON disk.id = t1.id
WHERE rankNum >= 2;

For Demo Follow the below link:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=318e94a135853fcd15b14e4b8bbf1fdc

回答4:

delete from disk
where id  in 
(
select id 
from task
group by id 
having count(id) >1
)

回答5:

No need to create any temporary tables

I hope this will work for you

DELETE ColumnName
FROM TableName
INNER JOIN 
(
    SELECT  MAX(ID) AS ID
    FROM TableName
    GROUP BY ID
    HAVING COUNT(*) > 1
) Duplicate on Duplicate.ID = TableName.ID
WHERE TableName.ID < Duplicate.lastId;

Please also check the following link your for more suggestions

MySQL delete duplicate records but keep latest