delete duplicate entry in mysql without temporary tables

Question

I have deleted duplicate rows in a mysql table using temporary tables. Is it possible to do it without using it? I have searched a lot but did not find any solution.

number | status | service
1234 | A | x1
1234 | D | x1
1234 | D | x1
1234 | A | x2
1234 | D | x2
2345 | A | x1

My basic need is that there should be only one row which is instatus d for a perticular number and service. there are a lot of entries in the table

It depends. You have to be much more specific and provide table schema , sample data, desired outcome — peterm, Aug 06 '13 at 20:12
possible duplicate of [Delete duplicate records without creating a temporary table](http://stackoverflow.com/questions/8590010/delete-duplicate-records-without-creating-a-temporary-table) — Preet Sangha, Aug 06 '13 at 20:13
@peterm: i have editted the question . my requirement is that duplicate entries with status 'D' should be deleted. — Darshan Gopal R, Aug 06 '13 at 20:30
Are these all columns in your table? Do you have any id column? — peterm, Aug 06 '13 at 20:35
@peterm: There is one more column which is serial number and is auto incremented — Darshan Gopal R, Aug 06 '13 at 20:37

score 2 · Answer 1 · answered Aug 06 '13 at 20:13

2

You can try something like this:-

DELETE t1 FROM table t1, table t2 WHERE t1.name = t2.name AND t1.id > t2.id;

provided name and id are the columns in table

Checkout this link

answered Aug 06 '13 at 20:13

Rahul Tripathi

161,154
30
262
319

i can not use operators as it is a character which is a duplicate. – Darshan Gopal R Aug 06 '13 at 20:32

peterm · Accepted Answer · 2013-08-06T20:54:36.563

A possible solution is to let MySql do the job applying a UNIQUE constraint with IGNORE option (which will treat errors as warnings) effectively de-duplicating the table

ALTER TABLE Table1 ENGINE MyISAM; -- this obviously needed only if your ENGINE is not MyISAM
ALTER IGNORE TABLE Table1 ADD UNIQUE (`number`, `status`, `service`);
ALTER TABLE Table1 ENGINE InnoDB; -- again this is only needed if your ENGINE was other than MyISAM

After that you'll get:

| NUMBER | STATUS | SERVICE |
-----------------------------
|   1234 |      A |      x1 |
|   1234 |      A |      x2 |
|   1234 |      D |      x1 |
|   1234 |      D |      x2 |
|   2345 |      A |      x1 |

Here is SQLFiddle demo

Since ...There is one more column which is serial number and is auto incremented... another option is to use a query like this

DELETE t
  FROM Table1 t JOIN
(
SELECT number, status, service, MAX(serial) serial
  FROM Table1
 GROUP BY number, status, service
HAVING COUNT(*) > 1
) q ON t.number = q.number
   AND t.status = q.status
   AND t.service = q.service
   AND t.serial <> q.serial

Outcome is the same:

| SERIAL | NUMBER | STATUS | SERVICE |
--------------------------------------
|      1 |   1234 |      A |      x1 |
|      4 |   1234 |      A |      x2 |
|      3 |   1234 |      D |      x1 |
|      5 |   1234 |      D |      x2 |
|      6 |   2345 |      A |      x1 |

Here is SQLFiddle demo

delete duplicate entry in mysql without temporary tables

2 Answers2