CODEWITHSUNDEEP
mysqlsql
mfrancisp
mfrancisp

Reputation: 119

Query for updating a table value based on the total of a column found in multiple tables

I'm trying to write a query to sum all the prc values from different tables and UPDATE it to main_trans.tot.

ex. tot value for TR01 should be 30 adding all TR01 prcs from two tables.

Table main_trans:

| id | tot | 
|TR01|  30 |
|TR02|  5  |

TABLE sub_trans_a:

| id | prc |
|TR01|  10 |
|TR01|  10 |

TABLE sub_trans_b:

| id | prc |
|TR01|  10 |
|TR02|  5  |

I don't know how to write it so that it would automatically update all rows based on their id. So far, my query does the work if i specifically write the value of the id column:

UPDATE main_trans  SET tot =  
(SELECT SUM(prc) FROM sub_trans_a WHERE id = 'TR01')
+ (SELECT SUM(prc) FROM sub_trans_b WHERE id = 'TR01') 
WHERE id = 'TR01'

Upvotes: 1

Views: 917

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If you want to do the update for all at the same time, just use correlated subqueries:

UPDATE main_trans mt
    SET tot = ( (SELECT SUM(prc) FROM sub_trans_a a WHERE a.id = mt.id) +
                (SELECT SUM(prc) FROM sub_trans_b b WHERE b.id = mt.id) 
              );

If one or both tables may not have values, then the result might be NULL. You can fix this using COALESCE():

UPDATE main_trans mt
    SET tot = ( COALESCE((SELECT SUM(prc) FROM sub_trans_a a WHERE a.id = mt.id), 0) +
                COALESCE((SELECT SUM(prc) FROM sub_trans_b b WHERE b.id = mt.id), 0)
              );

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use a join in your update query with a union set 

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,SUM(prc) prc FROM sub_trans_a WHERE id = 'TR01'
union all
SELECT id,SUM(prc) prc FROM sub_trans_b WHERE id = 'TR01'
) t1
) t
on(t.id = m.id)
SET m.tot = t.prc 
WHERE m.id = 'TR01'

Also if you have same structure for sub_trans_a and sub_trans_a so why 2 tables why not just a single table or with a single column for the type as type a or type b

See Demo

Or if you want to update your whole main_trans table without providing id values you can do so by adding a group by in query

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,SUM(prc) prc FROM sub_trans_a group by id
union all
SELECT id,SUM(prc) prc FROM sub_trans_b group by id
) t1  group by id
) t
on(t.id = m.id)
SET m.tot = t.prc

See Demo 2

Edit a good suggestion by Andomar you can simplify inner query as

UPDATE main_trans m  
join
(SELECT id,SUM(prc) prc
FROM (
SELECT id,prc FROM sub_trans_a
union all
SELECT id,prc FROM sub_trans_b 
) t1 WHERE id = 'TR01'
) t
on(t.id = m.id)
SET m.tot = t.prc 
WHERE m.id = 'TR01'

Upvotes: 1

Related Questions