Introduction to the method of MySql updating data through query result set

This article brings you an introduction to the method of MySql updating data through query result sets. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Table structure

   现在有用户表和用户实名认证表，user_info，user_card。
   user_info中的字段有user_id，user_name 。
   user_card中的字段有user_id，user_card，user_name 。
   其中user_name为用户实名认证的信息，user_info中做了字段冗余。

Problem

   用户表user_info中的user_name和user_card中的user_name不同步。
   user_card中有值，user_info中没有值。
   需要将user_card中的user_name同步到user_info中去。

Solution

1. Query the data with empty user_name in user_info through the code, and then query it through user_id User real-name authentication data is synchronized.

     select user_id from user_info where user_name = '' ;

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

     通过代码更新数据

2. Update data after joint table query

    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. Update data inline through 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 ;

This article is all over here, more For other exciting content, you can pay attention to the MySQL Tutorial Video column on the PHP Chinese website!

The above is the detailed content of Introduction to the method of MySql updating data through query result set. For more information, please follow other related articles on the PHP Chinese website!