'How to delete a record from database where all fields are the same to another?

I have two only records in a database table and I want to delete only one of them. The problem is that I don't have any primary key nor unique identifier, so how could I delete one and only one record?

It seems a easy question but I didn't find out how to do it ¿?.

CREATE TABLE `ToDo` (
  `id` bigint(20) NOT NULL,
  `caption` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  `done` tinyint(1) DEFAULT NULL,
  `idUser_c` int(11) DEFAULT NULL,
  `idUser_u` int(11) DEFAULT NULL,
  `idUser_d` int(11) DEFAULT NULL,
  `date_c` datetime DEFAULT NULL,
  `date_u` datetime DEFAULT NULL,
  `date_d` datetime DEFAULT NULL,
  `version` bigint(20) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ToDo` (`id`,`caption`,`description`,`priority`,`done`,`idUser_c`,`idUser_u`,`idUser_d`,`date_c`,`date_u`,`date_d`,`version`) VALUES (3,'hello','how are you',2,0,1,1,1,'2018-03-03 13:35:54','2018-03-03 13:35:57','2018-03-03 13:36:00',0);
INSERT INTO `ToDo` (`id`,`caption`,`description`,`priority`,`done`,`idUser_c`,`idUser_u`,`idUser_d`,`date_c`,`date_u`,`date_d`,`version`) VALUES (3,'hello','how are you',2,0,1,1,1,'2018-03-03 13:35:54','2018-03-03 13:35:57','2018-03-03 13:36:00',0);
sqlmariadb


Solution 1:[1]

This addresses the title, which implies potentially more than 2 rows in the table:

CREATE TABLE new LIKE ToDo;
INSERT INTO new
    SELECT DISTINCT id, caption, ...
        FROM ToDo;
RENAME TABLE ToDo TO old,
             new TO ToDo;
DROP TABLE old;

Solution 2:[2]

Well, what a good reason for an auto-incremented column! Well, you can add one:

alter table todo add ToDoId int auto_increment primary key;

This also sets the value.

Then you can do:

delete td
    from todo td join
         todo td1
         on td.id = td1.id and td.caption = td1.caption and . . . and
            td1.id < td.id;

This assumes that the columns are not NULL.

Alternatively, fix the entire table:

create temporary table temp_todo as
    select *
    from todo;

truncate table todo;

insert into todo
    select distinct *
    from todo;

This handles NULL values better than the first version.

Along the way, fix the table to have an auto-incremented primary key, so you can avoid this problem forevermore.

Solution 3:[3]

I think I found it myself, I just got stuck for a sec!

DELETE FROM ToDo WHERE ... LIMIT 1;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rick James
Solution 2
Solution 3 Joe

Related Questions