• I have updated an existing table with a column called "Expected Profit".

• All the existing records created before now have this field with a null value.

• I want to write SQL statement to update that null value using some combination of some of the values already in that table.

The formula would be something like this ( all those are values existing in the table ) :

Expected Profit = quantity * (1 - failure_rate) * expected_sales_price - (product_fixed_cost   young_product_cost_value)

Now I want to update it with SQL statement, but I dont really know how to do it :

UPDATE tablePlannedSales
SET expected_profit = (*SQL I should use to set the formula above*)
WHERE expected_profit IS NULL

Any help would be very welcome,

CodePudding user response：

In case all columns that you need within your formula are part of the table you want to update, it's not required do a select. You can just execute the update command according to your example:

UPDATE tablePlannedSales 
  SET expected_profit = quantity * (1 - failure_rate) * expected_sales_price - (product_fixed_cost   young_product_cost_value) 
WHERE expected_profit IS NULL

If this does not work as expected, something with your formula is wrong or some columns within the formula are not present in this table.