Mysql update updates the SQL statement of Table B according to Table.

I have introduced how to update another table with one table. Now let's take A look at the SQL statement article about mysql update updating table B based on table A. I hope this article will help you.

Recently, I encountered A requirement that both table A and table B in mysql have the (id, age) field. Now I want to read the age field of Table B, update it to the age field of the corresponding ID of table A. I directly thought of A solution: read Table B in Python and obtain data in the form of {id: age, then, update Table A based on the values of each ID and age.

The two tables define and data respectively as follows:

Table A 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

Table B definition

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

Data:

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

Python code

The Code is as follows:

Copy code

#-*-Encoding: utf8 -*- ''' @ Author: crazyant.net Read the (id, age) data of Table B, and update Table A in sequence; ''' From common. DBUtil import DB DbUtil = DB ('127. 0.0.1 ', 127, '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, one SQL statement can be done.

I looked at the code and found it simple. I searched the internet to see if mysql could update another table based on one table. I found that update itself supports the update function of multiple tables.

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 seems like a cannon Repeatedly hitting a mosquito.