This is my query, I need to write an update that will set mabi accountid = mb glinventory for each of the outputs that this select statement gives. This is what my select statement pulls

Basically, I need to set the mabi accountid = mb glinventory for the rows in my output if that makes sense

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;

This is the select statement that pulls those outputs, I need to write an update to do what I described above. Im not sure of the best way to do it, I have tried

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;

I've done things similar, is this the right route, how would I complete this task. I effectively need to do

UPDATE mabi
SET mabi.accountid = mb.glinventory
WHERE mabi.itemid = mb.itemid

CodePudding user response：

Your approach is actually correct. Just to combine it all together like this will do

UPDATE mabi
SET mabi.accountid = mb.glinventory
FROM mabi
JOIN mb
ON mabi.itemid = mb.itemid

If you need to check and filter by mabi.accountid, you can add a WHERE condition at the back. Like where mabi.accountid = 0

CodePudding user response：

You are almost there, by the looks. You just need to join mabi and MB together:

UPDATE
  mabi
SET
  mabi.accountid = mb.glinventory
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.accountid = mabi.accountid;