sql根据子表批量更新主表

@进行中

已于 2022-11-17 11:03:54 修改

阅读量831

点赞数

分类专栏： sql 文章标签： sql 数据库

于 2022-11-17 11:02:04 首次发布

本文链接：https://blog.csdn.net/lu1171901273/article/details/127899884

版权

sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

UPDATE t_main a1 INNER JOIN
(
SELECT main_id,SUM(weight) AS weight,SUM(money) AS money FROM detail_table
WHERE del_flag = FALSE
GROUP BY main_id
) a2 ON a1.main_id = a2.main_id
SET a1.weight = a2.weight,a1.money=a2.money

2、更新

UPDATE table
    SET weight = #{dto.weight} ,
    state =
    CASE
    WHEN money &gt; #{dto.money} THEN
    '02'
    WHEN money &lt;= #{dto.money} THEN
    '03' ELSE '01'
    END
    WHERE
id = #{dto.id}