MySQL学习笔记:从一个表update到另外一个表
# ---- 测试数据 ----
# 表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