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.
This a great post, thank you for sharing good stuff with us...
ReplyDeleteBest Wedding Photographers | Wedding Photographers in Rohini | Wedding Photographers in Pitampura | Wedding Photographers in Delhi
Mua vé tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ tháng nào rẻ nhất
chuyến bay cứu trợ mỹ về việt nam
vé máy bay từ đức về việt nam
thông tin chuyến bay từ nga về việt nam
bay từ anh về việt nam
ve may bay tu phap ve viet nam