CODEWITHSUNDEEP
mysqlsql-delete
Nemanja Srećković
Nemanja Srećković

Reputation: 359

How to delete multiple entries in mysql

I have db with multiple entries.

I Google out something like this

SELECT COUNT(*), item_id, text, number FROM ads
GROUP BY item_id, text, number
HAVING COUNT(*)>1;

this select (I think) all my multiple entries, I use SQLyog, ... and there is no option to press button and delete all results of this query.

but even if I select all one by one and delete, I would also delete original one, right?

I basically want to keep all unique entries and keep one of multiple items.

simple example

('1' 'Novi Sad' '123'); ('1' 'Novi Sad' '123'); ('3' 'Beograd' '124');

I want to keep

('1' 'Novi Sad' '123'); ('3' 'Beograd' '124');

I know only basic mysql.

Upvotes: 0

Views: 498

Answers (4)

Ashish Bhavsar
Ashish Bhavsar

Reputation: 236

DELETE emp FROM employee emp, employee emp2
WHERE emp.id > emp2.id
AND emp.name = emp2.name

For example, you having the table employee in which there are duplicate records (having the same name multiple times) then this query will delete all the duplicate records.

Upvotes: 0

z-boss
z-boss

Reputation: 17608

Select all unique records into a temp table.
Delete all records from original table.
Insert all records from your temp table into original table.

Upvotes: 1

Nick Craver
Nick Craver

Reputation: 630349

Can you just copy, drop and delete?

CREATE TABLE Copy_Temp as
SELECT item_id, text, number 
FROM ads
GROUP BY item_id, text, number;

DROP Table ads;

RENAME TABLE Copy_Temp TO ads;

Upvotes: 1

novwhisky
novwhisky

Reputation: 485

When you do delete entries make sure to reset your ID increment

ALTER TABLE 'table_name' AUTO_INCREMENT = 1

Upvotes: 1

Related Questions