Error while Dropping Duplicate Rows in MySQL table

Question

--Table Schema

CREATE TABLE Test(
ID INT, 
FirstName Varchar(100), 
LastName Varchar(100), 
Country Varchar(100) ); 

Insert into Test (FirstName,LastName,Country)values('Raj','Gupta','India'),
                                ('Raj','Gupta','India'),
                                ('Mohan','Kumar','USA'),
                                ('James','Barry','UK'),
                                ('James','Barry','UK'),
                                ('James','Barry','UK');

alter table Test modify column ID int primary key auto_increment;

--To delete duplicate rows

Delete * from Test where id not IN 
(select min(id) from Test 
 group by FirstName,LastName,Country
);

--output error Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' from Test where id not IN (select min(id) from Test group by Firs' at line 1*

There is no `Delete *` , just `delete` https://dev.mysql.com/doc/refman/8.0/en/delete.html — Ergest Basha, Oct 21 '22 at 10:23

score 0 · Answer 1 · edited Oct 21 '22 at 10:22

0

Delete 
from Test 
where id not IN ( select min(id) 
                  from Test 
                  group by FirstName,LastName,Country 
                 );

edited Oct 21 '22 at 10:22

Ergest Basha

7,870
4
8
28

answered Oct 21 '22 at 10:19

pragni kadia

1

Error while Dropping Duplicate Rows in MySQL table

1 Answers1