mysql子查询更新问题

最新推荐文章于 2024-07-22 03:13:47 发布

原创最新推荐文章于 2024-07-22 03:13:47 发布 · 2.9k 阅读

1 ·

CC 4.0 BY-SA版权

【mysql】专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种在MySQL中更新表数据时的常见问题及解决方案。通过对原始SQL语句进行重构，将子查询转化为虚拟表的方式，实现了对同一表查询后再进行修改的操作，提高了执行效率。

mysql不支持对同一个表查询后做修改(update、delete)操作，是其功能问题。

原来的sql语句：

UPDATE t_collection_contract_base base
SET base.total_interest_penalty = (SELECT
                                SUM(rp.remain_interest_penalty)
                              FROM t_xyb_contract_repayment_plan rp
                              WHERE rp.contract_id = base.contract_id
                                  AND rp.state IN('3', '4')),
  base.late_fee = (SELECT
                SUM(rp.remain_late_fee)
              FROM t_xyb_contract_repayment_plan rp,t_collection_contract_base  
              WHERE rp.contract_id = base.contract_id
                  AND rp.state IN('3', '4')),
  base.plan_total_amount = (SELECT
                         SUM(remain_total_amount)
                       FROM t_xyb_contract_repayment_plan rp
                       WHERE rp.contract_id = base.contract_id
                           AND rp.state IN('3', '4'))
WHERE base.is_late = "Y"

修改后的sql：

把子查询做一次转换，成为虚拟表，即可正常，此为优化后的语句。

UPDATE t_collection_contract_base AS base INNER JOIN (SELECT
    rp.contract_id AS contractid,
    SUM(rp.remain_interest_penalty) AS penalty,
    SUM(rp.remain_late_fee) AS fee,
    SUM(remain_total_amount) AS amount
  FROM t_xyb_contract_repayment_plan rp
  WHERE rp.contract_id 
      AND rp.state IN('3', '4') GROUP BY rp.contract_id)AS abc 
ON base.contract_id=abc.contractid
SET base.total_interest_penalty =abc.penalty,
	base.plan_total_amount=abc.fee,
	base.plan_total_amount=abc.amount
WHERE base.is_late = "Y"