mysql putty command delete duplicates

Question

I like to delete duplicate links from mysql database

From phpmyadmin this command is ok for smal database but get error about some time i have 5gb data in the table

DELETE t2 FROM Link t1 JOIN Link t2 ON (t2.page = t1.page AND t2.linkID > t1.linkID);

I like to delete duplicate links from putty ssh but get error:

[root@server]# mysql -p

Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11433 Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select database
    -> DELETE t2 FROM   Link t1 JOIN   Link t2 ON (t2.page = t1.page AND t2.linkID > t1.linkID);
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 'DELETE t2 FROM   Link t1 JOIN   Link t2 ON (t2.page = t1.page AND t2.linkID > t1' at line 2
mysql>

how to?

The error is from the first line of your query. 'select database' should be 'use databasename;' (change 'databasename' to the actual name), and at the end of each separate query you need to put a semi colon. Although your DELETE query has syntax error too. — sn00k4h
i have select a good database name this its an exemple maybe nedd some simbol how is format for imput? with putty? — Olariu Romeo Vicentiu

Ahmad Ahmad · Accepted Answer · 2012-10-24T05:34:05

It is best to delete using a simpler query like this

DELETE FROM T2 WHERE LINK IN 
(SELECT T2.LINK FROM T1 JOIN T2 ON (T2.PAGE=T1.PAGE AND T2.LINKID > T1.LINKID))

mysql putty command delete duplicates

1 Answers