How to delete a certain row from MySQL table with same column values - MySQL Developer Tutorial

How to delete a certain row from MySQL table with same column values

Let's say you have a table with some columns and the table have duplicate rows. You need to delete the row with same values in other row.

Let's create the customer table and insert duplicate records (same records) and then we can delete the duplicate row.

CREATE TABLE `customer` (
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

Insert duplicate records. In this example, you can see that first two rows are duplicate records.

insert into customer
(firstname,lastname,age,phonenumber,dob,gender)
values
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir','shahzad',39,'505-424000','1980-01-01','M'),
('Aamir2','',40,'505-4141000','1980-02-01','M'),
('Aamir3',' ',41,'505-4141000','1980-02-01','M')
;

If we need to delete one record out of duplicate record from above customer table, we can use below delete statement. We used Limit to handle this situation. Limit 1 will return only 1 record.

Delete from customer
where firstname='Aamir' and lastname='shahzad'
limit 1;

Let's say if you have 5 duplicate records and you need to delete 4 out of them, then you will be using Limit 4.