希望大家帮帮忙,update的多表连接
最近项目有个需求,mysql一条update语句实现批量更新数据的东西
表结构如下
olduser表
------------------------
username | userid
李明 196
张三 145
-----------------------
authors表
-----------------------
author | authorid
李明 null
张三 null
-----------------------
update authors set authorid=
(select olduser.userid from olduser,authors where authors.author=olduser.username) //以中文名实现关联
where author=(select username from olduser,authors where authors.author=olduser.username ) //错误的行
问题:想这么写的,但是where条件返回的是多个值,可定不能一一对应,望高手赐教
------解决方案--------------------
create table newuser as select * from olduser o,authors a where a.author=o.username
update newuser set uerid=authorid
alter table newuser drop author
alter table newuser drop authorid