Need to update column from derived column of select statement

I’ve this below select statement. Over there I’ve 2 derived columns EmpContbtnWithoutTax and EmpContbtnWithTax I wanted to have that data permanently in that same table EmpPFContributionTest

Need help to have that update query for EmpPFContributionTest table which update the respective rows of EmpContbtnWithoutTax and EmpContbtnWithTax columns permanently. Update script which I was trying for 1 column first mentioned below

update  t2
set EmpContbtnWithoutTax = 
(
 select  case when sum(isnull(t1.emp_contribution,0)) over(partition by 
 t1.emp_no order by (t1.pcm_year * 100 + t1.pcm_month)) + 
 sum(isnull(vpf,0)) over(partition by t1.emp_no order by (t1.pcm_year * 
 100 + t1.pcm_month)) < 3000 
            then sum(isnull(t1.emp_contribution,0)) over(partition by 
  t1.emp_no order by (t1.pcm_year * 100 + t1.pcm_month)) + 
  sum(isnull(vpf,0)) over(partition by t1.emp_no order by (t1.pcm_year * 
  100 + t1.pcm_month))
       else null
       end
     from EmpPFContributionTest t1
   )
   from EmpPFContributionTest t2

/*Actual Select Statement */

select  case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) + sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) < 3000 
        then sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) + sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month))
   else null
   end
   empcontbtnwithouttax,
   case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) + sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) >= 3000
        then sum(isnull(emp_contribution,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month)) + sum(isnull(vpf,0)) over(partition by emp_no order by (pcm_year * 100 + pcm_month))
   else null
   end      
   empcontbtnwithtax,
* from [dbo].EmpPFContributionTest
where
(pcm_year * 100 + pcm_month >= 201504) AND
(pcm_year * 100 + pcm_month < 201604)
 and emp_no= 11101201 
order by (pcm_year * 100 + pcm_month)

Answer

It look like you’re trying to update two existing columns in the same table?

It’s not a good pattern to use where the data is computed from existing data – when the data changes your computed values are instantly invalidated.

Having said that, to do what you are asking you can simply use an updatable CTE, something like:

with tax as (
    select case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) + sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) < 3000 
             then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) + sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month)
         else null
         end as withouttax,
         case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) + sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) >= 3000
                    then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month) + sum(isnull(vpf,0)) over(partition by emp_no order by pcm_year * 100 + pcm_month)
         else null
         end as withtax
    from dbo.EmpPFContributionTest
    where
    pcm_year * 100 + pcm_month >= 201504
    and pcm_year * 100 + pcm_month < 201604
    and emp_no = 11101201
)
update tax set
EmpContbtnWithoutTax = withouttax
empcontbtnwithtax = withtax