Question

sql中delete嵌套的问题

有个表，例如下表（打比方，实际表很复杂）
authorid message
1 。。。
1 。。。
1 。。。
1 。。。
2 。。。
2 。。。
message字段内容省略，我想删除发表message个数小于3的，在该表中是要删除authorid为2的，一下为实际表中的语句
delete from posts_99 where authorid in (select authorid from posts_99 group by authorid having count(message)<3)
这个语句怎么会有问题呢，怎么改都不行。高手有啥好的方法吗？
mysql中报错信息为：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 'betwwen (select authorid from posts_99 group by authorid having count(message)<1' at line 1
这个语句delete from posts_99 where count(message)<10;也不行啊，烦啊
他说You can't specify target table 'posts_99' for update in FROM clause

Answer 1

delete from posts_99 where authorid in (select authorid from posts_99 group by authorid having count(message)<3)
中select authorid from posts_99 group by authorid having count(message)<3
查询条件中没有count(message)，所以having条件不成立。
select authorid，count(message) from posts_99 group by authorid having count(message)<3 这样的查询才成立，但在delete语句中不适用。
用两步来解决
1。将查询结果放到一个临时表中
select authorid，count(message) SL into #a from posts_99 group by authorid having count(message)<3
2.关联临时表做delete

delete from posts_99 where authorid in (select authorid from #a)

Answer 2

delete from posts_99 where authorid in (select authorid from posts_99 group by authorid having count(authorid)<3)

Answer 3

直接用count(*)
delete from posts_99 where authorid in (select authorid from posts_99 group by authorid having count(*)<3)