MySQL update SQL statement for table B based on table A

Recently encountered a requirement: MySQL in a table and B table have (ID, age) field, now you want to read the Age field of Table B and update it to the age field of the corresponding ID in table A, and I have a direct idea: Read B table in Python and get data in the form of {id:age}. Then update a table according to the value of each ID and age.

Two tables are defined separately and the data are as follows:

A table definition:

Field	Type	Comment
Id	Int (11)
Name	varchar (20)
Age	Int (11)

Data:

1,name1,0
2,name2,0
3,name3,0
4,name4,0
5,name5,0

b Table Definition

Field	Type	Comment
Id	Int (11)
Age	Int (11)

Data:

1,11
2,21
3,31
4,41
5,51

Python code to implement the

The code is as follows

Copy Code

#-*-Encoding:utf8-*- ''' @author: Crazyant.net Read the (ID, age) data in table B, and then update a table sequentially; ''' From common. Dbutil Import DB Dbutil = DB (' 127.0.0.1 ', 3306, ' root ', ', ', ' test ') rs = Dbutil.query ("Select Id,age from Table_b") For row in RS: (idv,age) =row Print (Idv,age) update_sql= "Update table_a set age= '%s ' where id= '%s ';"% ( AGE,IDV) Print Update_sql Dbutil.update (Update_sql) print ' over '

In fact, a single SQL statement can be done

Look at the code, it is simple, so the internet search the MySQL can be based on a table to update another table, the results found that the update itself to support the function of multiple table updates.

The code is as follows	Copy Code
UPDATE table_a,table_b SET table_a.age=table_b.age WHERE table_a.id=table_b.id;

Using Python code, it appears to be a cannon hit the mosquito many times in one fell swoop.