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 :)
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