CODEWITHSUNDEEP
mysqlsqlduplicates
user9673975
user9673975

Reputation: 3

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's 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.

What should the query for it look like?

Upvotes: 0

Views: 736

Answers (5)

Nishant Gupta
Nishant Gupta

Reputation: 3656

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

Upvotes: 1

DEarTh
DEarTh

Reputation: 1005

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;

Upvotes: 0

Hardik Masalawala
Hardik Masalawala

Reputation: 1066

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

Upvotes: 0

Klodian
Klodian

Reputation: 713

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;

Upvotes: 6

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

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

Upvotes: 0

Related Questions