'Update null value on existing column/tables using aggregation

  • 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,

sqlpostgresql


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions