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 ¿?.
x
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);
Advertisement
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;