最近项目有个需求,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条件返回的是多个值,可定不能一一对应,望高手赐教
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条件返回的是多个值,可定不能一一对应,望高手赐教
UPDATE AUTHORS
SET AUTHORID = (SELECT OLDUSER.USERID
FROM OLDUSER, AUTHORS
WHERE AUTHORS.AUTHOR = OLDUSER.USERNAME LIMIT 1)
WHERE AUTHOR = (SELECT USERNAME
FROM OLDUSER, AUTHORS
WHERE AUTHORS.AUTHOR = OLDUSER.USERNAME LIMIT 1)
You can't specify target table 'authors' for update in FROM clause
(select olduser.userid from olduser where authors.author=olduser.username)
where exists (select username from olduser where authors.author=olduser.username )
`author` varchar(15),
`authorid` mediumint(8),
PRIMARY KEY (`author`)
)
CREATE TABLE `olduser` (
`username varchar(15),
`uerid` mediumint(8),
PRIMARY KEY (`username`)
) INSERT INTO `authors` (`author`, `authorid`) VALUES
('张三', 1),
('李四', 2),
('王五', 3);
INSERT INTO `olduser` (`username`, `userid`) VALUES
('张三', 0),
('李四', 0),
('王五', 0);
SET AUTHORID = (SELECT OLDUSER.USERID
FROM OLDUSER, AUTHORS
WHERE AUTHORS.AUTHOR = OLDUSER.USERNAME)
UPDATE AUTHORS A1
SET A1.AUTHORID = (SELECT U1.USERID
FROM OLDUSER U1
WHERE A1.AUTHOR = U1.USERNAME)
WHERE EXISTS (
SELECT 1
FROM OLDUSER U1
WHERE A1.AUTHOR = U1.USERNAME
);
(select olduser.userid from olduser where authors.author=olduser.username) 这样就可以
update newuser set uerid=authorid
alter table newuser drop author
alter table newuser drop authorid