Home > Database > Mysql Tutorial > body text

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

不言
Release: 2019-03-20 11:21:43
forward
3394 people have browsed it

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中做了字段冗余。
Copy after login

Problem

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

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 (上面的结果集) ;

     通过代码更新数据
Copy after login

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 = '';  

    通过代码更新数据
Copy after login

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 ;
Copy after login

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!

Related labels:
source:segmentfault.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template