Tuesday, May 24, 2011

MYSQL - Deleting duplicated entries huge DB

The problem occurred when i had to delete duplicated entries

like this:

id name value
502 john null
503 john null
1000 john smith

solution for this was to group by name and export them to other table

so here is mysql codes:

1. Exporting to new table
CREATE TABLE new-table-name as
SELECT * FROM old-table-name WHERE 1 GROUP BY [column by which to group];

2. Renaming old and new tables ( 'different-new-table-name' , not new-table-name )
RENAME TABLE old-table-name TO different-new-table-name;

3. And finaly renaming new table to old table's name
RENAME TABLE new-table-name TO old-table-name;

No comments:

Post a Comment