CODEWITHSUNDEEP
mysql
user3796596
user3796596

Reputation: 21

SQL Update table based on another table

I have 2 tables T1 (month+year is unique)

 month  year    amount 
  10      2015     5000  
  11      2015     4000

T2

  month   year     amount 
  10      2015      100
  10      2015      200
  10      2015      200
  11      2015      200

I want to update the amount field of T1 as follows T1.amount = T1.amount - (sum of amount in T2 for matching month,year)

So after update T1 shall look like this

  month    year   amount
     10       2015    4500 
     11       2015    3800

I am not expert at SQL, kindly suggest SQL to achieve the above, thanks !

Upvotes: 0

Views: 77

Answers (2)

1000111
1000111

Reputation: 13519

UPDATE T1 
INNER JOIN 
(    
  SELECT 
    `month`,
    `year`,
     SUM(amount) totalAmount
  FROM T2
  GROUP BY `month`,`year`  ) t
ON t.`month` = T1.`month` AND t.`year` = T1.`year`
SET T1.amount = T1.amount - t.totalAmount;

SQL FIDDLE

Explanation:

The inner query will sum up the amount for each (month,year) pair.

Inner Query:

SELECT 
`month`,
`year`,
SUM(amount) totalAmount
FROM T2
GROUP BY `month`,`year`

For your sample data it returns result like below:

I named it t table

month   year totalAmount

10      2015       500

11      2015       200

Now if you join this t table with your T1 table ON month and year

that means rows from two tables will be joined only if T1.month equals t.month and T1.year equals t.year AND only then you will update the corresponding amount of T1 table's row like below:

T1.amount = T1.amount - t.totalAmount

So after this update your T1 table should look like below:

SELECT 
*
FROM T1

Result:

month     year    amount

10        2015     4500

11        2015     3800

Edit: You tagged the question MySQL while you are expecting query to work in SqlLite. SQLlite doesn't support JOIN in Update statements.

Sqllite query:

UPDATE T1
SET T1.amount = T1.amount - (
    SELECT
        SUM(T2.amount)
    FROM
        T2
    WHERE
        T1.`month` = T2.`month`
    AND T1.`year` = T2.`year`
);

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can do an UPDATE with a JOIN to a derived table containing the sum per month/year of Table2:

UPDATE TABLE1 AS T1
JOIN ( SELECT month, year, SUM(amount)  AS amount
       FROM TABLE2 
       GROUP BY month, year) AS T2
  ON T1.month = T2.month AND T1.year = T2.year
SET T1.amount =   T1.amount - T2.amount;

Demo here

Edit:

In sqlite you have to use a correlated subquery to do the UPDATE:

UPDATE TABLE1 
SET amount =  amount - (SELECT SUM(amount)  
                        FROM TABLE2 
                        WHERE TABLE1.month = TABLE2.month AND 
                        TABLE1.year = TABLE2.year);

Demo here

Upvotes: 1

Related Questions