更新数据库中的值 [英] update values from a database

问题描述

大家好，



我正在尝试将一个应用程序添加到我的网站，其中旧产品超过一年，它们会自动出现在折扣中（-20％），

所以我有一个数据表产品（有colums Id，名称，价格，日期）

thnx到我之前的问题digimanus给了我一个查询我收集旧产品<选择名称，价格从产品WHERE（DATEDIFF（m，日期，GETDATE（））> 12）>



现在我想降低每个旧的价格

所以我写下面的代码

问题是它只更新上一个产品的价值和价格来自其他旧产品从最后一个旧产品中获得价格

我应该改变什么才能使其正常工作？

或者其他方式更简单这一个？

thnx提前为您提供帮助！

Hi guys again,

I'm trying to add an application to my web site where the "old" products older than a year they appear automatically in discount (-20%),
so i have a datatable products (with colums Id,name,price,date)
thnx to my previous question digimanus gave me a query where i collect the "old products" <select name,price FROM products WHERE (DATEDIFF(m, date, GETDATE()) > 12)>

now i want to reduce the price of every "old"
so i writte the following code
the problem is that it's update the value only from the last product and the prices from the other "old" products receive the price from the last "old" product
what i should change in order to make it work?
or is any other way simpler from this one?
thnx in advance for your help!!!

SqlCommand sqlCommand = new SqlCommand("select name,price FROM products WHERE (DATEDIFF(m, date, GETDATE()) > 12)", conn);
       conn.Open();
       using (SqlDataReader read = sqlCommand.ExecuteReader())
       {
           while (read.Read())
           {
               string namep = read["name"].ToString();
               string pice = read["price"].ToString(); //current price
               int discountprice = Convert.ToInt32(pice);
               updtable(Convert.ToInt32(pice));
           }

           read.Close();
           conn.Close();
       }


   }
   private void updtable(int pricenew)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
       int newvalue = pricenew - ((20 * pricenew) / 100); //discount ammount
       string aSQL = "UPDATE products set price= " + newvalue + "Where (DATEDIFF(m, date, GETDATE()) > 12) AND var=1";
       try
       {
           con.Open();
           SqlCommand cmd = new SqlCommand(aSQL, con);
           SqlDataReader reader = cmd.ExecuteReader();
           reader.Read();
           DataTable dataTable = new DataTable();
           dataTable.Load(reader);
       }
       finally
       {
           con.Close();
       }
       int temp = 0;
       updtable2(temp);
   }
   private void updtable2(int var)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
       string aSQL = "UPDATE products set var= " + var + "Where (DATEDIFF(m, date, GETDATE()) > 12)";
       try
       {
           con.Open();
           SqlCommand cmd = new SqlCommand(aSQL, con);
           SqlDataReader reader = cmd.ExecuteReader();
           reader.Read();
           DataTable dataTable = new DataTable();
           dataTable.Load(reader);
       }
       finally
       {
           con.Close();
       }

推荐答案

只需使用T-SQL命令即可实现：

You can achieve that using only T-SQL command:

UPDATE P1
SET P1.price = P2.price - (P2.price * 20 /100)
FROM Products AS P1 INNER JOIN Products AS P2 ON P1.ProductID = P2.ProductID
WHERE DATEDIFF(m, P1.[date], GETDATE()) > 12

这篇关于更新数据库中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！