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

问题描述

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

 Table_User 
 id name total 
 1 A 
 2 B 

 Table_Product 
 id产品价格
 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 *来自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（））; 
 
总计+ =金额; 
 con.Open（）; 
 cmd1 =  new  SqlCommand（  update tbl_user set total = @ total其中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 ）; 

[edit]已添加代码块 - OriginalGriff [/ edit]

解决方案

试试这个：

 更新 U  SET  U.total = S.total 
  FROM  Table_User U 
  JOIN （ SELECT  P.id  AS  id，
 SUM（P.price） as 总计
  FROM  Table_Product P 
  GROUP   BY  P.id）S 
  ON  U.id = S.id 

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
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");

[edit]Code block added - OriginalGriff[/edit]

解决方案

Try this:

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屋！