MySQL 为什么不允许delete语句使用表别名
展开全部
可以用别名,只是你写法写错了。
DELETE t1 FROM test AS t1 WHERE .....
参见 http://dev.mysql.com/doc/refman/5.7/en/delete.html
DELETE t1 FROM test AS t1 WHERE .....
参见 http://dev.mysql.com/doc/refman/5.7/en/delete.html
2014-08-22
展开全部
有一个表的数据比较大,然后需要进行关联删除,删除的时候发现如下SQL报错:
DELETE FROM test.test1 a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.smsInfoID AND b.receiveTime <="2011-10-09 00:00:00" ) limit 1000;
报错如下:
Error Code: 1064
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 'a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.smsInf' at line 1 www.2cto.com
开始以为是exists的问题,于是更改成in再测试,也报同样错:
DELETE FROM test.test1 a WHERE a.recordid IN (SELECT b.smsInfoID FROM test.test2 b WHERE b.receiveTime <= "2011-10-09 00:00:00") LIMIT 1000;
原来是MYSQL的delete语句不支持别名!
将别名去掉就可以了,将上面两个报错的SQL更改成如下形式就可以了:
DELETE FROM test.test1 WHERE recordid IN (SELECT smsInfoID FROM test.test2 WHERE receiveTime <= "2011-10-09 00:00:00") LIMIT 1000; www.2cto.com
DELETE FROM test.test1 WHERE EXISTS (SELECT 1 FROM test.test2 WHERE test1.recordID=test2.smsInfoID AND test2.receiveTime <= "2011-10-09 00:00:00") LIMIT 1000;
转自:http://www.2cto.com/database/201204/127360.html
DELETE FROM test.test1 a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.smsInfoID AND b.receiveTime <="2011-10-09 00:00:00" ) limit 1000;
报错如下:
Error Code: 1064
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 'a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.smsInf' at line 1 www.2cto.com
开始以为是exists的问题,于是更改成in再测试,也报同样错:
DELETE FROM test.test1 a WHERE a.recordid IN (SELECT b.smsInfoID FROM test.test2 b WHERE b.receiveTime <= "2011-10-09 00:00:00") LIMIT 1000;
原来是MYSQL的delete语句不支持别名!
将别名去掉就可以了,将上面两个报错的SQL更改成如下形式就可以了:
DELETE FROM test.test1 WHERE recordid IN (SELECT smsInfoID FROM test.test2 WHERE receiveTime <= "2011-10-09 00:00:00") LIMIT 1000; www.2cto.com
DELETE FROM test.test1 WHERE EXISTS (SELECT 1 FROM test.test2 WHERE test1.recordID=test2.smsInfoID AND test2.receiveTime <= "2011-10-09 00:00:00") LIMIT 1000;
转自:http://www.2cto.com/database/201204/127360.html
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
你在什么情况下使用别名,删除时应该用不到别名吧
追问
我个人习惯在表操作的时候给表起别名
我就是好奇为啥mysql 规定delete语句不能起别名,十分好奇
追答
这个回答不了,对不起……
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询