Setting a field value from another field value in the same table

Transact-SQL
1

I want to set remaining field values to be the first field value I set like setting defaults. I do a calculation to get field1 value:

SET FCMP = cost * multiplier,
Price2 = FCMP,
Price3 = FCMP

is this doable and if not is there a way to do this?

The calculation to get FCMP is much more complicated than above and it involves a few sub queries but for my example I made it simple.

2

The query optimizer "should" be able to recognize that the three calculations are identical and perform it only once:SET FCMP = cost * multiplier, Price2 = cost * multiplier, Price3 = cost * multiplier

You could guarantee that there would be only a single calculation by using a CTE:;with cte as ( select cost * multiplier new_cost ) select FCMP = new_cost, Price2 = new_cost, Price3 = new_cost from cte(it's nor clear if you are using variables, since there are no @'s in the names, or if you are using columns names since there is no table referenced)

3

You can use a CROSS APPLY(s) within the query to assign alias names to calculations. You can then use those names in future CROSS APPLYs or in the main SELECT:

SELECT FCMP, Price3, ...
FROM table_name
CROSS APPLY (
    SELECT FCMP = cost * multiplier
) AS ca1
CROSS APPLY (
    SELECT Price2 = FCMP, Price3 = FCMP
) AS ca2
--...