Mysql update select usage example
Inner join should be used, I .e:
UPDATE friends inner join users ON friends. friendid = users. userid SET friends. friendname = users. username
MySQL uses a temporary table to implement nested queries in The FROM clause. Therefore, nested queries can be added to another nested query so that the FROM clause can be queried and saved in the temporary table, then indirectly referenced in the peripheral query.
Let's look at the following SQL statement:
Update apples
Set price = (
Select price from (
Select * from apples
) As x
Where variety = 'Gala ')
Where variety = 'Fuji ';
Continue to see the following two instances:
Update a set a. xx = (select yy from B) where a. id = B. id;
However, in mysql, the result of set select cannot be used directly. You must use inner join:
Update a inner join (select yy from B) c on a. id = B. id set a. xx = c. yy
For example, the next classic Mysql update statement assigns values to nested select instances.
Update mem_player set 'datawarehouse' = (select 'datawarehouse' from (select * from mem_player) as B where 'pid' = 100000)
Note: For more exciting articles, please follow the help houseProgramming TutorialTopic.