CODEWITHSUNDEEP
mysqlsql
user3529351
user3529351

Reputation: 43

How to delete rows in MySQL which have same value for a column

I have something like this: 

col1 col2 col3
A      B   C
A      B   C

I want only one row to remain out of these and other to be deleted. What query to implement?

Upvotes: 0

Views: 1054

Answers (2)

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

If you want both duplicates to be removed, try this:

DELETE m.*
FROM mytable m
INNER JOIN
  (SELECT col1,
    col2,
    col3,
    COUNT(*)
  FROM mytable
  GROUP BY col1,
    col2,
    col3
  HAVING COUNT(*)>1
  ) t
ON m.col1 =t.col1
AND m.col2=t.col2
AND m.col3=t.col3

SQL Fiddle

EDIT

As you have edited your question and want one out of two duplicate rows to remain, you can easily achieve this with the following line:

  ALTER IGNORE TABLE mytable ADD UNIQUE INDEX idx_yourindexname (col1, col2, col3);

This will remove all of your duplicate rows, so that only unique ones are kept. The IGNORE keyword in compulsory because you already have data disobeying the INDEX in your table. Movereover, this will not allow for the duplicate insertions in the future.

Upvotes: 2

splash21
splash21

Reputation: 809

You can try;

delete from table where col1 = 'A' and col2 = 'B' and col3 = 'C' limit 1;

You could use a record count, etc. in the limit clause to delete more records.

Upvotes: 0

Related Questions