SQL - Updating / Computing values from different rows but same column-CodePudding

I have a table / dataset that looks like this:

headerid personid accountid value
---------------------------------
123      P1       Account1  5000
123      P1       Account2  1000
123      P2       Account1  3000
123      P2       Account2  500

I would like to update accountid = account1 with the difference of account1 and account2 for the same personid (p1) and headerid (123). After running the script the resulting data should look like:

headerid personid accountid value
---------------------------------
123      P1       Account1  4000
123      P1       Account2  1000
123      P2       Account1  2500
123      P2       Account2  500

Thing to note is I need to update all person's account1 values with the script. I am using SQL SERVER 2017.

CodePudding user response：

One option is to use conditional aggregation to determine whether accountid equals Account1 or not, along with SUM() OVER () window function as grouping by the headerid and personid columns

WITH t2 AS
(
 SELECT *,
        SUM(CASE WHEN accountid = 'Account1' THEN value ELSE -value END)
           OVER(PARTITION BY headerid, personid) AS new_value
   FROM t
)
UPDATE t2
   SET value = new_value
  FROM t2
 WHERE t2.accountid = 'Account1'

Demo

CodePudding user response：

Your sample data

DROP 
  TABLE IF EXISTS #mytable;
  CREATE TABLE #mytable(
  headerid VARCHAR(50) NOT NULL, 
  personid VARCHAR(50), 
  accountid VARCHAR(50), 
  value int
);
INSERT INTO #mytable(headerid,personid,accountid,value) VALUES 
('123', 'P1', 'Account1', 5000), 
('123', 'P1', 'Account2', 1000), 
('123', 'P2', 'Account1', 3000), 
('123', 'P2', 'Account2', 500);

You should join your table with itself in order to subtract values as follows

UPDATE m1
SET    m1.value = m1.value - m2.value
FROM   mytable m1
       JOIN mytable m2
         ON m1.headerid = m2.headerid
            AND m1.personid = m2.personid
            AND m1.accountid = 'Account1'
            AND m2.accountid = 'Account2'