# ---- 测试数据 ----
# 表1
CREATE TABLE temp_x AS
SELECT 1 AS c_id, 1.11 AS c_amount FROM DUAL
UNION ALL
SELECT 2 AS c_id, 1.22 AS c_amount FROM DUAL;
# 表2
CREATE TABLE temp_y AS
SELECT 1 AS c_id, 1.43 AS c_amount FROM DUAL
UNION ALL
SELECT 2 AS c_id, 1.44 AS c_amount FROM DUAL;
# 查询
SELECT * FROM temp_x;
SELECT * FROM temp_y; # 恢复数据
UPDATE temp_x SET c_amount = 1.11 WHERE c_id = 1;
UPDATE temP_x SET c_amount = 1.22 WHERE c_id = 2;
# 报错 不可执行
UPDATE temp_x a
SET a.`c_amount` = b.c_amount
FROM temp_y b
WHERE a.`c_id` = b.c_id;
# 还是报错
UPDATE temp_x a
SET a.`c_amount` = b.`c_amount`
FROM temp_x a, temp_y b
WHERE a.`c_id` = b.`c_id`;
可行的办法：
# 方法一 可行
UPDATE temp_x a, temp_y b
SET a.`c_amount` = b.`c_amount`
WHERE a.`c_id` = b.`c_id`;
# 方法二 可行
UPDATE temp_x a
SET a.`c_amount` = (SELECT b.c_amount FROM temp_y b
WHERE b.`c_id` = a.`c_id`)
# 方法三 可行 方法二加强版
UPDATE temp_x a
SET a.c_amount = (SELECT b.c_amount
FROM temp_y b
WHERE b.c_id = a.c_id)
WHERE a.c_id IN (SELECT b.c_id FROM temp_y b);
其他例子：
# update 一列
UPDATE student s, city c
SET s.city_name = c.name
WHERE s.city_code = c.code;
# update 多列
UPDATE a, b
SET a.title = b.title
a.name = b.name
WHERE a.id = b.id;
# 通过子查询
UPDATE student s
SET s.city_name =
(SELECT NAME FROM city
WHERE CODE = s.city_code);
# 复杂查询
UPDATE a SET
xx = (SELECT b.xxx FROM b WHERE b.id = a.id),
yy = (SELECT b.xxx FROM b WHERE b.id = a.id)
WHERE EXISTS(SELECT b.xxx FROM b WHERE b.id = a.id);
# 优化写法
UPDATE a INNER JOIN b ON a.id = b.id
SET a.xx = b.xxx
a.yy = b.xxx;
END 2018-05-29 17:01:00