MySQL moves from one table update field to another
- 2020-11-18 06:30:58
- OfStack
Let's start with a few simple examples
Solution 1:1
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.name
where a.id=b.id
Solution 3: Subquery
update student s set city_name = (select name from city where code = s.city_code);
Let's look at a couple more of the ones that did it
For example, update the table tk_zyt_scenery_order to t_advs_order.
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;