Mysql子查询的更新

 有两张表,一张是管理员信息表Admin_user,一张是临时表tmp_Admin_User。当临时表中的用户名和管理员信息表中的用户名相同时,要把临时表中ErrorType字段设置为1。

通常的写法是:

Update `tmp_Admin_User` Set `ErrorType`=1 Where `login_name` in (Select A.`login_name` from `tmp_Admin_User` as A Inner Join `Admin_User` as B On A.`login_name`=B.`login_name`)

但是MySql会报错:#1093 – You can’t specify target table ‘tmp_admin_user’ for update in FROM clause

可以使用的写法应该是:

Update `tmp_Admin_User` as C,(select A.`login_name` from `tmp_admin_user` as A Inner Join `admin_user` as B On A.`login_name`=B.`login_name`) as D set `ErrorType`=1 Where C.`login_name` = D.`login_name`

 原文:http://gee218.blog.163.com/blog/static/4489680820094224133268/