如何根据另一个表值更新表值 [英] How to Update Table Value based on Another Table Value

问题描述

我有两个表格如下所示，我想在按下时更改按钮然后在Table_User中存储尊重ID的总金额。示例：在Table_Product中，ID 1为Purchase 3 Product（100 + 400 + 100）= 600保存在Table_user中，ID为1总计。



Table_User

I have two table as per Below, I want to change when I press Button Then Store the total amount in respect id in Table_User. Example : In Table_Product, ID 1 is Purchase 3 Product (100+400+100) = 600 is save in Table_user in ID 1 Total.

Table_User

id  name total      
1   A      
2   B

Table_Product

Table_Product

id  product price
1   ABC    100
1   BCA     400
2   PQR     500
2   IJH     200
3   RET    300
1   OOO    100

protected void btnTotal_Click(object sender, EventArgs e)
{
   cmd = new SqlCommand("select * from tbl_product", con);
   da = new SqlDataAdapter(cmd);
   dt = new DataTable();
   da.Fill(dt);

   int i;
   for (i = 0; i <= dt.Rows.Count-1; i++)
   {
      int amount = Convert.ToInt16(dt.Rows[i]["price"].ToString());

      total += amount;
      con.Open();
      cmd1 = new SqlCommand("update tbl_user set total=@total where id=@id",con);
      cmd1.Parameters.AddWithValue("@total",total);
      cmd1.Parameters.AddWithValue("@id",dt.Rows[i]["id"].ToString());
      cmd1.ExecuteNonQuery();
      con.Close();

   }
   Response.Redirect("User.aspx");
}

推荐答案

如果要代表另一个表更改表中的数据，可以使用触发器。表插入数据，更新数据和删除任何数据时触发触发。对于任何类型的信息只需点击此链接 触发器 - SQL Server [ ^ ]

点击回复任何查询

Happy Coding☺

if you want changed the data in table behalf of another Table you can use trigger. Trigger fire when table insert data,Update Data and Delete any data. for any type of information just hit to this link Triggers -- SQL Server[^]
hit to reply for any query
Happy Coding ☺

尝试：

Try:

UPDATE U SET U.total = S.total
FROM Table_User U
JOIN (SELECT P.id AS id,
             SUM(P.price) as total
      FROM Table_Product P
      GROUP BY P.id) S
ON U.id = S.id

这篇关于如何根据另一个表值更新表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！