CODEWITHSUNDEEP
sqlmariadb
Joe
Joe

Reputation: 7919

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

Upvotes: 0

Views: 175

Answers (3)

Rick James
Rick James

Reputation: 142296

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;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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.

Upvotes: 1

Joe
Joe

Reputation: 7919

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

DELETE FROM ToDo WHERE ... LIMIT 1;

Upvotes: 0

Related Questions