mysql结果集更新_MySql通过查询结果集更新数据

解决方法

1.通过代码查询出user_info中 user_name 为空的数据 ,然后通过user_id查询出用户实名认证的数据进行同步 。

select user_id from user_info where user_name = '' ;

select * from user_card where user_id in (上面的结果集) ;

通过代码更新数据

2.联表查询后更新数据

SELECT

c.user_id ,

c.user_name

FROM

user_info AS u

LEFT JOIN user_card AS c ON u.user_id = c.user_id

WHERE

u.user_name = '';

通过代码更新数据

3.通过MySql内联更新数据

先写出更新语句

UPDATE `user_info` as u SET u.user_name = '结果集' ;

再获取条件结果集

SELECT

c.user_id ,

c.user_name

FROM

user_info AS u

LEFT JOIN user_card AS c ON u.user_id = c.user_id

WHERE

u.user_name = '';

最后内联更新

UPDATE `user_info` as u

INNER JOIN

(

SELECT

c.user_id ,

c.user_name

FROM

user_info AS u

LEFT JOIN user_card AS c ON u.user_id = c.user_id

WHERE

u.user_name = '';

) as r ON u.user_id = r.user_id SET u.user_name = r.user_name ;