Chinaunix首页 | 论坛 | 博客
  • 博客访问: 7586452
  • 博文数量: 368
  • 博客积分: 9600
  • 博客等级: 上校
  • 技术积分: 18875
  • 用 户 组: 普通用户
  • 注册时间: 2009-01-01 00:00
文章分类

全部博文(368)

文章存档

2017年(9)

2016年(19)

2015年(3)

2014年(6)

2013年(8)

2012年(78)

2011年(66)

2010年(135)

2009年(44)

分类: Mysql/postgreSQL

2012-04-09 18:29:55

    有一个表的数据比较大,然后需要进行关联删除,删除的时候发现如下SQL报错:

点击(此处)折叠或打开

  1. 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;
报错如下:

点击(此处)折叠或打开

  1. Error Code: 1064
  2. 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再测试,也报同样错:

点击(此处)折叠或打开

  1. 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;
将别名去掉就可以了,将上面两个报错的SQL更改成如下形式就可以了:

点击(此处)折叠或打开

  1. DELETE FROM test.test1 WHERE recordid IN (SELECT smsInfoID FROM test.test2 WHERE receiveTime <= "2011-10-09 00:00:00") LIMIT 1000;
  2. 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;

通过网友的指点,发现可以通过别名删除,只是语法有点奇怪,下面支持别名的语法:

点击(此处)折叠或打开

  1. DELETE test.a 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;
感谢win_study网友!



阅读(16191) | 评论(6) | 转发(1) |
给主人留下些什么吧!~~

飞鸿无痕2012-04-16 15:52:19

win_study: 不好意思,试试。
DELETE test.a FROM test.test1 as a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.sms.....

http://database.51cto.com/ar.....
太感谢了,按照你的方法操作成功。

win_study2012-04-16 15:22:42

飞鸿无痕: 你测试了吗?? 我这边按照你说的更改不行。报这个错误。Unknown table 'test1' in MULTI DELETE.....
不好意思,试试。
DELETE test.a FROM test.test1 as a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.sms.....

http://database.51cto.com/art/201005/202216.htm

飞鸿无痕2012-04-16 08:53:41

win_study: T-SQL的 Delete 支持别名的写法,改成这样就可以了。
DELETE test.test1 FROM test.test1 a WHERE EXISTS (SELECT 1 FROM test.test2 b WHERE a.recordID=b.sms.....
你测试了吗?? 我这边按照你说的更改不行。报这个错误。Unknown table 'test1' in MULTI DELETE

win_study2012-04-14 15:05:15

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;

飞鸿无痕2012-04-11 17:54:29

天的星星: mysql的update语句支持limit吗? ......
支持的!