[MySQL]Remove spaces and delete dupes

[LazyManc]

I've got a database table with a couple of hundred thousand records. The table has a unique field, "tel1" which holds telephone numbers (as a varchar).

Some of the numbers are formatted with spaces, i.e. "0845 000 0000"

and some aren't i.e. "08450000000".

I'd like to remove all the spaces, and any duplicate records.

I've tried

UPDATE table SET tel1 = replace(tel1, " ", "");

but that gives an error if there is a duplicate record (because tel1 is unique).

Any way around this?

 

[LazyManc]

The problem is they aren't dupes until the phone number has had the spaces removed... so I can't match them like that unfortunately

 

[LazyManc]

I made a slight adjustment (one of the tel1's will be the space-less version?) but I get an error when trying to run:

mysql> DELETE FROM test A WHERE id = (SELECT id FROM test B WHERE B.id <> A.id AND Replace(B.tel1, " ", "") = A.tel1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = (SELECT id FROM test B WHERE B.id <> A.id AND Replace(B.tel1, " ", ""' at line 1

 

[LazyManc]

mysql> DELETE FROM `test` WHERE Replace(' ', '', `test`.`tel1`) IN (SELECT `test`.`tel1` FROM `test`);
ERROR 1093 (HY000): You can't specify target table 'test' for update in FROM clause


Apologies if i'm not much use here, my SQL experience is pretty much limited to inserts and updates.

 

[LazyManc]

Ok,

SELECT `id`, `tel1` FROM `test` AS `A` WHERE Replace(`A`.`tel1`, " ", "") IN (SELECT `A`.`tel1` FROM `test`);

Gives the correct records i.e. the dupes.

but

DELETE FROM `test` AS `A` WHERE Replace(`A`.`tel1`, " ", "") IN (SELECT `A`.`tel1` FROM `test`);

gives a syntax error.