正常来说咱们编写sql语句中的修改语句中是不涉及from关键词的,但是今日在公司编写修改连表修改的需求时需要用到该关键词from
UPDATE refund t1 SET refundoriginalamount = t2.org
FROM(
SELECT
r.orderid id,
((o.originalamount/i."count")*r."count") org
FROM refund r
LEFT JOIN "order" o ON o.objectid = r.orderid
LEFT JOIN orderitem i ON i.orderid = o.objectid
) t2
where t1.orderid = t2.id;
该脚本的目的是为了在refund表格中对于新增加字段refundoriginalamount的填充数据,但是字段refundoriginalamount的数据需要根据表格order和orderitem与refund中数据共同计算得出,故有了该语句