DELETEFROM 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
开始以为是exists的问题,于是更改成in再测试,也报同样错:
点击(此处)折叠或打开
DELETEFROMtest.test1 a WHERE a.recordid IN(SELECT b.smsInfoID FROMtest.test2 b WHERE b.receiveTime <="2011-10-09 00:00:00") LIMIT 1000;
T-SQL的 Delete 支持别名的写法,改成这样就可以了。
DELETE test.test1 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;