mysql删除数据

1. 在使用delete删除select根据exists关联出的数据后，发现报错。

1.1 表test_1:

mysql> select * from test_1;

+------+------+

| id   | name |

+------+------+

|    1 | a    | 

|    1 | b    | 

|    2 | c    | 

+------+------+

3 rows in set (0.00 sec)

1.2 表test_2:

mysql> select * from test_2;

+------+------+

| id   | name |

+------+------+

|    2 | B    | 

|    1 | A    | 

+------+------+

2 rows in set (0.00 sec)

2. 需要删除test_2表中存在与test_1表中并且在test_1是重复的数据：

mysql> select * from test_2 a where exists (select * from test_1 b where a.id = b.id group by b.id having count(*) > 1);  

+------+------+

| id   | name |

+------+------+

|    1 | A    | 

+------+------+

1 row in set (0.00 sec)

3. 删除该数据：

mysql> delete from test_2 a where exists (select * from test_1 b where a.id = b.id group by b.id having count(*) > 1);         

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 exists (select * from test_1 b where a.id = b.id group by b.id having coun' at line 1

4. 错误信息显示语法问题，因为在delete的时候不能用别名：

4.1 举个简单的例子：

mysql> select * from test_3;

+------+------+

| id   | name |

+------+------+

|    3 | C    | 

|    4 | D    | 

+------+------+

2 rows in set (0.00 sec)

4.2 带别名删除数据：

mysql> delete from test_3 a where a.id = '3';

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 a.id = '3'' at line 1

4.3 修改去掉别名删除都没有问题：

mysql> delete from test_3 where id = 3;

Query OK, 1 row affected (0.00 sec)

mysql> delete from test_3 where test_3.id = 4;

Query OK, 1 row affected (0.00 sec)

5. 回到最初的问题，去掉别名，删除成功：

mysql> delete from test_2 where exists (select * from test_1 where test_1.id = test_2.id group by test_1.id having count(*) > 1);                 

Query OK, 1 row affected (0.00 sec)

mysql> select * from test_2;

+------+------+

| id   | name |

+------+------+

|    2 | B    | 

+------+------+

1 row in set (0.00 sec)