Menu
I am running a query successfully using in MySQL 5.5
SELECT columnA FROM table GROUP BY columnA HAVING count(*) > 1 However, I need to run this same query using DELETE and I'm a little unsure how to delete ? i.e. the returned results should be deleted ?
Any ideas ?
636
SQL delete duplicate Rows using Group By and having clause In this method, we use the SQL GROUP BY clause to identify the duplicate rows. The Group By clause groups data as per the defined columns and we can use the COUNT function to check the occurrence of a row.
ALTER TABLE tbl_Country DROP COLUMN IsDeleted, DROP COLUMN CountryName; This allows you to DROP , ADD and ALTER multiple columns on the same table in the one statement. From the MySQL reference manual: You can issue multiple ADD , ALTER , DROP , and CHANGE clauses in a single ALTER TABLE statement, separated by commas.
Place it in a subquery:
delete from table where columnA in ( select columnA from ( select columnA from YourTable group by columnA having count(*) > 1 ) t ) 
delete from YourTable where YourTable.columnA in (select columnA from YourTable group by column A having count(*) > 1) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
