CODEWITHSUNDEEP
mysql
Zero
Zero

Reputation: 1

Delete Duplicate Records

How would I delete all duplicate data from a MySQL Table, without altering / adding any extra column? 

SELECT * FROM webs;

+--------+
|  web   |
+--------+  
| google |
| google | 
| msn    |
| yahoo  |   
| msn    |
| yahoo  |
+--------+

Result should be:

+--------+
|  web   |
+--------+
| google |
| yahoo  | 
| msn    |
+--------+

Upvotes: 0

Views: 84

Answers (3)

java-dev-ua
java-dev-ua

Reputation: 79

SELECT DISTINCT web FROM table_name;

UPD

INSERT INTO tempTableName(cellId,attributeId,entityRowId,value) SELECT DISTINCT cellId,attributeId,entityRowId,value FROM tableName;

https://stackoverflow.com/a/5016434/8483171

Upvotes: 0

LogicalAnt
LogicalAnt

Reputation: 963

DELETE d1 FROM `webs` d1 
INNER JOIN `webs` d2
WHERE d1.column_name = d2.column_name AND d1.id>d2.id;

Hope this would help. This will check a single column as your table has a single column for duplication check. Replace column_name with the actual column name.

EDIT:

If the table has only single column

ALTER IGNORE TABLE `webs` ADD UNIQUE(web)

Upvotes: 0

Vergiliy
Vergiliy

Reputation: 1356

You can implement this through a temporary table:

CREATE TABLE temp SELECT DISTINCT * FROM webs;
DROP TABLE webs;
ALTER TABLE temp RENAME TO webs;

Upvotes: 1

Related Questions