如何使用两张基础表的数据更新tp表的total_cost列？

原语句的核心问题 #

• 两个子查询均重复关联了tp表，未限定仅匹配当前正在更新的行，会返回所有符合条件的记录，触发Subquery returns more than 1 row错误。
• 无法精准关联tp表单行对应的hv、tourism数据，逻辑上无法得到预期的单条记录计算结果。

正确实现方案 #

方案一：JOIN方式更新（性能更优） #

通过JOIN直接关联三张表，精准匹配每行数据后完成更新，逻辑清晰且执行效率更高：

UPDATE tp
JOIN hv ON tp.h_id = hv.hh_id
JOIN tourism ON tp.t_id = tourism.t_id
SET tp.total_cost = hv.h_package + tourism.t_package;

若tp中存在未匹配到hv或tourism的行，可使用LEFT JOIN配合COALESCE处理NULL值（将NULL转为默认值0）：

UPDATE tp
LEFT JOIN hv ON tp.h_id = hv.hh_id
LEFT JOIN tourism ON tp.t_id = tourism.t_id
SET tp.total_cost = COALESCE(hv.h_package, 0) + COALESCE(tourism.t_package, 0);

方案二：关联子查询方式 #

若需保留子查询写法，必须确保子查询仅返回当前更新行的对应值，通过WHERE子句关联当前行：

UPDATE tp
SET total_cost = (
    SELECT h_package FROM hv WHERE hv.hh_id = tp.h_id
) + (
    SELECT t_package FROM tourism WHERE tourism.t_id = tp.t_id
);

同样，处理未匹配的NULL值：

UPDATE tp
SET total_cost = COALESCE(
    (SELECT h_package FROM hv WHERE hv.hh_id = tp.h_id), 0
) + COALESCE(
    (SELECT t_package FROM tourism WHERE tourism.t_id = tp.t_id), 0
);

内容的提问来源于stack exchange，提问作者Bkaur