Shulou(Shulou.com)06/01 Report--

This article will explain in detail how MySql updates data through the query result set. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Table structure

There are now user tables and user real name authentication tables, user_info,user_card. The field in user_info has user_id,user_name. The field in user_card has user_id,user_card,user_name. User_name is the user's real-name authentication information, and field redundancy is done in user_info.

problem

The user_name in the user table user_info is out of sync with the user_name in user_card. There is a value in user_card, but no value in user_info. You need to synchronize the user_name in user_card to user_info.

Solution method

1. The data with empty user_name in user_info is queried by code, and then the data authenticated by user's real name is queried by user_id to synchronize.

Select user_id from user_info where user_name =''; select * from user_card where user_id in (the result set above); update the data through code

two。 Update data after join 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 =''; update data through code

3. Update data inline through MySql

First write the update statement UPDATE `user_ info` as u SET u.user_name = 'result set', and then get the conditional result set 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 ='' Finally, inline update 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 is all about how MySql updates the data through the query result set. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.