mysql复杂delete操作

对表进行update或者delete操作时，mysql不支持在where语句后面进行自身的查询，举个例子

delete from chat_room where id in(select max(id) as id
 from chat_room 
where user_to_id>0
GROUP BY create_user_id,user_to_id 
having count(create_user_id)>1)

以上语句先是在where语句中查询了自己，然后再delete。不符合mysql的规范。
有的人搞了一层中间表，是可以的。只要where后面不是直接查询就可以。

也可以利用Join语句，让自身表不出现在where条件中。

delete t1 from chat_room t1 join (select max(id) as id
 from chat_room 
where user_to_id>0
GROUP BY create_user_id,user_to_id 
having count(create_user_id)>1)t2
on t1.id = t2.id

delete还支持join操作

delete t1 from 
t1
join t2
on t1.id=t2.id
and  t2.name='xiaoming'