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

Answer

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;

Advertisement

Answer