0

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    |
+--------+
BenM
  • 52,573
  • 26
  • 113
  • 168
Zero
  • 1
  • 2
  • Is that the entire schema of your `webs` table? Do you want to remove the duplicate entries from the table, or just from the query you're running? – BenM Apr 16 '19 at 08:27
  • Yes want to delete duplicate entries and retain only single entry of each. – Zero Apr 16 '19 at 08:34
  • 1
    Possible duplicate of [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) – Mert Akkanat Apr 16 '19 at 08:34
  • That is case Sql Sever, In Mysql don't have rowid – Zero Apr 16 '19 at 08:41
  • Is there any other column in `webs`? In MySQL, you can't delete duplicates without adding/altering columns or creating temp table since there is no `rowid` or `rownum`. If there is any other `unique` column in your table then we can do it – Arun Palanisamy Apr 16 '19 at 11:17
  • No bro don't have any other column, Operation need to be done with respect to given data. – Zero Apr 16 '19 at 11:33

3 Answers3

1

You can implement this through a temporary table:

CREATE TABLE temp SELECT DISTINCT * FROM webs;
DROP TABLE webs;
ALTER TABLE temp RENAME TO webs;
Vergiliy
  • 1,248
  • 1
  • 13
  • 22
0
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)
LogicalAnt
  • 907
  • 3
  • 12
  • 28
0

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

java-dev-ua
  • 59
  • 9