如何编写SQL UPDATE语句实现mabi.accountid与mb.glinventory的匹配更新？

我需要编写一条UPDATE语句，将以下SELECT语句查询结果中对应行的mabi.accountid设置为mb.glinventory。

查询结果 #

简单来说，就是将查询结果中对应行的mabi.accountid替换为mb.glinventory。

用于获取结果的SELECT语句 #

SELECT
  mabi.businessactionitemid,
  mabi.accountid,
  mb.glinventory,
  mb.businessactionitemid
FROM
  mabusinessactionitem mabi
  INNER JOIN (
    SELECT
      mabi.accountid,
      cat.glinventory,
      mabi.businessactionitemid
    FROM
      cocategory cat
      INNER JOIN mabusinessactionitem mabi ON cat.categoryid = mabi.categoryid
    WHERE
      cat.categoryid = mabi.categoryid
      AND mabi.businessactionitemid IN (
        SELECT
          businessactionitemid
        FROM
          mabusinessactionitem mabi
          INNER JOIN mabusinessaction maba ON maba.businessactionid = mabi.businessactionid
        WHERE
          documentnumber = '32103'
          AND STATUS != 1
          AND accountid = 0
      )
  ) mb ON mb.businessactionitemid = mabi.businessactionitemid;

我已经尝试了下面这条UPDATE语句，但不确定是否正确，核心需求是实现UPDATE mabi SET mabi.accountid = mb.glinventory WHERE mabi.itemid = mb.itemid的逻辑：

UPDATE mabi
SET mabi.accountid = mb.glinventory
FROM mabusinessactionitem mabi
JOIN (
    SELECT mabi.accountid,
        cat.glinventory,
        mabi.businessactionitemid
    FROM cocategory cat
    INNER JOIN mabusinessactionitem mabi ON cat.categoryid = mabi.categoryid
    WHERE cat.categoryid = mabi.categoryid
        AND mabi.businessactionitemid IN (
            SELECT businessactionitemid
            FROM mabusinessactionitem mabi
            INNER JOIN mabusinessaction maba ON maba.businessactionid = mabi.businessactionid
            WHERE documentnumber = '32103'
                AND STATUS != 1
                AND accountid = 0
            )
    ) mb ON mb.businessactionitemid = mabi.businessactionitemid;

你尝试的UPDATE语句逻辑方向是对的，但可以优化结构，同时精准匹配你通过itemid关联的核心需求。

优化后的UPDATE语句 #

UPDATE mabusinessactionitem mabi
SET mabi.accountid = cat.glinventory
FROM cocategory cat
JOIN mabusinessactionitem mb_item ON cat.categoryid = mb_item.categoryid
JOIN mabusinessaction maba ON mb_item.businessactionid = maba.businessactionid
WHERE 
  mabi.itemid = mb_item.itemid
  AND maba.documentnumber = '32103'
  AND maba.STATUS != 1
  AND mabi.accountid = 0;

说明 #

1. 简化嵌套结构：把原SELECT中的多层子查询展开，提升语句可读性和执行效率。
2. 精准关联匹配：直接通过mabi.itemid = mb_item.itemid实现你想要的行匹配逻辑，确保更新的是对应数据。
3. 保留过滤规则：完整保留原查询中documentnumber = '32103'、STATUS != 1、accountid = 0的过滤条件，避免更新非目标数据。

验证建议 #

执行UPDATE前，先通过以下SELECT语句验证要更新的数据是否正确：

SELECT
  mabi.itemid,
  mabi.accountid AS 原accountid,
  cat.glinventory AS 要设置的accountid
FROM mabusinessactionitem mabi
JOIN cocategory cat ON cat.categoryid = mabi.categoryid
JOIN mabusinessaction maba ON mabi.businessactionid = maba.businessactionid
WHERE 
  maba.documentnumber = '32103'
  AND maba.STATUS != 1
  AND mabi.accountid = 0;

确认查询结果中的原accountid都是0，要设置的accountid符合预期后，再执行UPDATE语句，避免误操作。

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