更新mysql表中的列总和

Question

I have a table named resources with the following columns:

ress1 
ress2 
prod1 
prod2 

I've tried the code indicated in the following links, but I'm still stuck...

How to find sum of multiple columns in a table in SQL Server 2005?

Auto update sum of rows & columns in a table

SQL How to Update SUM of column over group in same table

I want to run a script to UPDATE ress1 = ress1 + prod1 and the same with the ress2. So I want to update into a column the sum of the column itself and the corresponding column, doing that for every rows from the table.

Here is some code that seems easy for me, but not working...

$sql    = "SELECT * FROM ro_map";
$result = mysqli_query($conn,$sql);
while($row = mysqli_fetch_assoc($result)){
$new_a = $row['a'] + $row['c'];
$new_b = $row['b'] + $row['d'];

"UPDATE ro_map SET a = '$new_a',b = '$new_b'";
} 

Ah...really sorry, guys :D I use mysql (with wampp server and HeidiSQL). And running this code don t give me any error ( if($conn->$sql == TRUE) will give me TRUE... SOLVED! Cool, thanks all, I've learned something :)

Answer 1

I think you're over-complicating it. If I understand you correctly, you can just do this in one transaction:

Update    resources
Set       ress1 = ress1 + prod1,
          ress2 = ress2 + prod2