php - 对特定 MySQL 表列求和并将结果写入其他列

Question

我有一个名为 Finance 的 MySQL 表，它有 id、balance、requested、ytotal6 和预算列。我想对余额列值求和并将结果写入 ytotal6，其中 id 为 1 列。

这是表结构link

这是我的index.php文件

$con=mysqli_connect("127.0.0.1","root","","dji001");
                $result = mysqli_query($con,"SELECT * FROM finance")
                or die("Error: ".mysqli_error($con));

                echo "<form style='width:1080px; backgrond-color:transparent;' action='update.php' method='post' class='form-group'>";
                    while($row = mysqli_fetch_array($result))
                    {  
                    $id = $row['ID'];
                    $Budget = $row['Budget'];
                    $Balance = $row['Balance'];
                    $Requested = $row['Requested'];
                    $balance = $Requested + $Budget;

                    $con->query("UPDATE finance SET balance = $balance WHERE id = $id");
                    echo "<div class='calc_container'> 

                    <input type='hidden' class='id' name='id[]' value='".$row['ID']."'>

                    <input type='text' class='budget' name='Budget[]' value='".$row['Budget']."'>

                    <input type='text' class='req_kbl' name='Requested[]' value='".$row['Requested']."'>

                    <input type='text' class='balance' name='Balance[]' value='".$row['Balance']."'>

                    </div>";}

                            $result = mysqli_query($con,"SELECT * FROM finance where id=1")
                            or die("Error: ".mysqli_error($con));
                            while($row = mysqli_fetch_array($result))
                            echo"
                            <input type='text' class='ytotal6' name='ytotal6' value='".$row['ytotal6'] ."'>";
                            echo "<input type='Submit' class='button' value='Submit'></form>";

我尝试过 $ytotal6 = sum[$balance]; 但没有成功。

Answer 1

对于“对余额列值求和并将结果写入 ytotal6，其中 id 为 1 列”尝试此操作

  UPDATE finance f1, (SELECT SUM(balance) AS bal
            FROM finance ) f2
  SET ytotal6 = bal
  WHERE f1.id = 1;

注意:对于像“You can't指定目标表在FROM子句中更新”这样的错误，我们需要创建一个临时表