mysql批量更新

假设有个user表

1 CREATE TABLE IF NOT EXISTS `user` (
2   `user_id` int(11) NOT NULL COMMENT '主键',
3   `user_level` int(11) NOT NULL DEFAULT 0 COMMENT '等级',
4   `user_name` varchar(32) NOT NULL DEFAULT 0 COMMENT '名称',
5   PRIMARY KEY (`user_id`)
6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

示例：

1 UPDATE user 
2             SET user_name = CASE user_id 
3                     WHEN 1 THEN '张三'
4                     WHEN 2 THEN '李四' 
5             END 
6 WHERE user_id IN (1,2);

如果需要更新多个值：

 1 UPDATE user 
 2             SET user_name = CASE user_id 
 3                     WHEN 1 THEN '张三'
 4                     WHEN 2 THEN '李四' 
 5             END ,
 6             user_level = CASE USER_ID 
 7                     WHEN 1 THEN 10
 8                     WHEN 2 THEN 20
 9             END
10 WHERE user_id IN (1,2);

 

posted @ 2017-06-30 10:17  L狗哥  阅读(182)  评论(0)  编辑  收藏  举报