Need to update column from derived column of select statement-CodePudding

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)

CodePudding user response：

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

CodePudding user response：

I've accomplished with view

                    CREATE OR ALTER function fn_GetEmpPFContribution
                (
                    @year nvarchar(4)
                )
                returns table
                as
                return(
                        select  case when sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month))   sum(isnull(vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month)) < 3000 
                                    then sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month))   sum(isnull(vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month))
                                    else null
                                end
                                [EmpContbtnWithoutTax],
                                case when sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pcm_year * 100   pfc.pcm_month))   sum(isnull(pfc.vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month)) >= 3000
                                     then sum(isnull(pfc.emp_contribution,0)) over(partition by pfc.emp_no order by (pcm_year * 100   pfc.pcm_month))   sum(isnull(pfc.vpf,0)) over(partition by pfc.emp_no order by (pfc.pcm_year * 100   pfc.pcm_month))
                                else null
                                end     
                                [EmpContbtnWithTax],
                                pfc.* 
                        from [dbo].[pf_contribution_master] pfc
                        where
                         (pfc.pcm_year * 100   pfc.pcm_month >= concat(@year,'04')) 
                        AND
                         (pfc.pcm_year * 100   pfc.pcm_month < concat(@year 1,'04'))
                      )