MySQL from one table update field to another table _ MySQL

With MySQL or higher, you can add two or more tables to one table. by adding two tables together, you can update the records of one table in the relevant fields and set them to another table. Let's take a few simple examples.

Solution 1: 1 column

update student s, city c  set s.city_name = c.name where s.city_code = c.code;

Solution 2: multiple columns

update a, b set a.title=b.title, a.name=b.namewhere a.id=b.id

Solution 3: subquery

update student s set city_name = (select name from city where code = s.city_code);

Let's take a look at a few

For example, if you update the field tk_zyt_scenery_order in the table to t_advs_order, it is generally written as follows:

UPDATE t_advs_order SET  attribute1=(SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`),  attribute2=(SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`)  WHERE EXISTS (SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id=`on`);

In this way, the efficiency is relatively low and the writing method is optimized:

UPDATE t_advs_order a INNER JOIN tk_zyt_scenery_order s ON s.order_id=a.`on` SET  a.attribute1=s.order_id,  a.attribute2=s.order_id;