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.

2 comments:

  1. Hello everyone, I wanted to share with you my recent experience with purchasing wholesale wedding dresses. I stumbled upon a European brand of designer wedding dresses that offers wholesale prices. The quality and designs of their dresses are outstanding and have been a huge hit with my customers. It's always great to find a reliable and affordable source for such an important aspect of a wedding. The ease of ordering and quick delivery time have made this brand my go-to for wholesale wedding dresses. I highly recommend checking out their collection for those in the wedding industry.

    ReplyDelete