Clever Geek Handbook

Incremental row value in SQL table

I have one table Metal_Master

in this column where Opening_Weight

I am updating the value of that column by selecting the previous value and adding some value and then updating that value again.

My code for this

ConnectionDB ReturnMWeight = new ConnectionDB("SELECT Opening_Weight FROM Metal_Master WHERE Metal_Name='GOLD';");
DataTable weighttd = ReturnMWeight.returntable();
GoldW = GoldW + Convert.ToDouble(weighttd.Rows[0][0].ToString());
ConnectionDB AddMWeight = new ConnectionDB("UPDATE Metal_Master SET Opening_Weight=" + GoldW + " WHERE Metal_Name='GOLD';");
AddMWeight.AddData();

But I want to directly update the value in one request. Please, help..

+3


source to share


3 answers


you can do UPDATE

directly without running the select statement,

UPDATE Metal_Master 
SET Opening_Weight = Opening_Weight + new_Value
WHERE Metal_Name='GOLD'


for better code quality,

  • use instructions using

    for proper disposal of objects.
  • use try-catch

    to handle unexpected exceptions correctly.
  • parameterized the query to prevent sql injection

+4


source


You can use the column name on the right side of the set.



 ConnectionDB AddMWeight = new 
 ConnectionDB("UPDATE Metal_Master SET Opening_Weight = Opening_Weight " +  10 + " WHERE Metal_Name='GOLD';");
+2


source


Try the following:

ConnectionDB AddMWeight = new ConnectionDB("UPDATE Metal_Master SET Opening_Weight=(SELECT SUM(Opening_Weight) AS Opening_Weight FROM Metal_master WHERE Metal_Name = 'GOLD')" + GoldW + " WHERE Metal_Name='GOLD';");
AddMWeight.AddData();
0


source






More articles:


All Articles