CODEWITHSUNDEEP
mysqlselectsql-update
Tiit Remmel
Tiit Remmel

Reputation: 230

MySQL update based on select

SELECT 
   table_1.id, 
   (SELECT SUM(amount) FROM table_2 WHERE table_1.id = table_2.bid) AS sum_total 
FROM table_1 
WHERE table_1.total!=0

I need to update table_1.total so that it would equal with sum_total.

Any adivce?

Upvotes: 1

Views: 1387

Answers (3)

Ariful Islam
Ariful Islam

Reputation: 7675

This simple query should work very well. 

UPDATE 
   table_1 
SET 
   table_1.total = (SELECT SUM(amount) FROM table_2 WHERE table_1.id = table_2.bid) 
WHERE table_1.total!=0;

Upvotes: 2

user319198
user319198

Reputation:

Try below:

update table_1 as t1 left join (select SUM(amount) as totalamount ,id FROM table_1 group by id) as t2 on t1.id =t2.id 
 set  t1.total=t2.totalamount

Please do needful changes.

Upvotes: 1

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

Does this work for you:

UPDATE table_1 set total = (SELECT SUM(amount) FROM table_1 WHERE table_1.id = table_1.bid) AS sum_total)

Upvotes: 1

Related Questions