How to include update stmts with Control expressions?

You cannot have an UPDATE statement in a CTE.

A CTE is a essentially a view with the scope of a single query. And just like a view, a CTE can only include a single SELECT statement.

If you want to use the same CTE with multiple statements (UPDATE or whatever), it may be more convenient to insert the result of the query in the CTE into a temp table.

Hi @Remo522 ,

As mentioned by other expert, you could not have update statement inside CTE.

Instead, you could use temp table in your situation and update the Final table accordingly.

Please refer one example from below and check whether it is helpful to you.

create table Final  
(  
claimid int identity(1,1),  
ClmSt varchar(100),  
EOMDate date,  
PendingCount int,  
NewCountFlag int,  
CloseFlag int,  
closeddate date,  
reopeneddate date  
)  
  
insert into Final(ClmSt,EOMDate,PendingCount,NewCountFlag,CloseFlag,closeddate,reopeneddate) values  
('AAA','2020-07-10',2,1,0,'2020-07-31','2020-07-01'),  
('BBB','2020-07-31',1,0,0,'2020-07-01','2020-07-31'),  
('CCC','2020-07-31',1,0,0,'2020-08-12',null),  
('DDD','2020-07-31',2,1,0,'2020-07-31','2020-07-30'),  
('EEE','2020-07-28',3,1,0,'2020-07-31','2020-07-01'),  
('FFF','2020-07-31',1,3,0,'2020-07-11',null),  
('GGG','2020-07-31',2,0,0,'2020-07-31','2020-07-01'),  
('HHH','2020-07-29',3,1,0,'2020-07-01','2020-07-31'),  
('III','2020-07-31',2,1,0,'2020-07-31','2020-07-01'),  
('JJJ','2020-07-31',2,1,0,'2020-07-02','2020-07-01'),  
('KKK','2020-07-28',2,1,0,'2020-08-05','2020-07-03'),  
('LLL','2020-07-31',1,4,0,'2020-07-09','2020-07-07')  
  
--select * from Final  
  
drop table if exists #TEMP1  
  
SELECT * INTO #TEMP1 FROM(  
select distinct a.claimid, 'PriorPending' ClmSt  
from Dbo.Final a  
where a.EOMDate = eomonth(dateadd(month,-1,GETDATE()))  
and a.PendingCount = 1  
union all  
select distinct a.claimid, 'CurNew' ClmSt  
from Dbo.Final a  
where a.EOMDate = (eomonth (DATEADD(MONTH, -1, GETDATE()) ))--'5/31/2019'  
and a.NewCountFlag = 1)a  
  
--select * from #TEMP1  
  
update a set a.closeflag=1  
from Final a  
where a.EOMDate = (eomonth (DATEADD(MONTH, -1, GETDATE()) ))--'5/31/2019'  
and year(a.closeddate) = year(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and month(a.closeddate) = month(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and a.claimid in ( select distinct l.claimid from #TEMP1 l)  
and a.closeflag = 0  
  
update a set a.closeflag=1  
from Final a  
where a.EOMDate = (eomonth (DATEADD(MONTH, -1, GETDATE()) ))--'5/31/2019'  
and year(a.closeddate) = year(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and month(a.closeddate) = month(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and year(a.reopeneddate) = year(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and month(a.reopeneddate) = month(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
and a.claimid not in ( select distinct l.claimid from #TEMP1 l)  
and a.reopeneddate < a.closeddate  
and a.closeflag = 0  
  
select * from Final  

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

If you want to get a query for your question, you need to provide table DDL and some sample data scripts and your expected result for these updates.

Give it a try.

I see now that you are only working with a single table all through the script, so it could indeed be possible to do the update in a single query. However it could be quite messy and difficult to understand. Well, it is difficult to understand already now with all those eomonth calls - I don't really think you need to nest them.

In any case, for questions of this type, we often recommend that you post the CREATE TABLE statement for your table together with INSERT statements with sample data, enough to illustrate all angles of the problem. And then we need to know the desired outcome given the test data. This permits us to copy and paste into a query window to develop a tested solution.

We also need a description of the business rules. That is, why you want that particular result.

Finally, it is always a good idea to include which version of SQL Server you are working on.

Hi @Remo522 ,

Please refer below which has the whole query run in one go:

;with cte as (  
 select distinct a.claimid, 'PriorPending' ClmSt  
 from Dbo.Final a  
 where a.EOMDate = eomonth(dateadd(month,-1,GETDATE()))  
 and a.PendingCount = 1  
 union all  
 select distinct a.claimid, 'CurNew' ClmSt  
 from Dbo.Final a  
 where a.EOMDate = (eomonth (DATEADD(MONTH, -1, GETDATE()) ))  
 and a.NewCountFlag = 1)  
      
  update a set a.closeflag= case when a.claimid in ( select distinct b.claimid from cte b) then 1   
  when year(a.reopeneddate) = year(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
 and month(a.reopeneddate) = month(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))   
and a.reopeneddate < a.closeddate  
 and a.claimid not in ( select distinct c.claimid from cte c) then 1   
 end   
   from Final a  
 where a.EOMDate = (eomonth (DATEADD(MONTH, -1, GETDATE()) ))--  
 and year(a.closeddate) = year(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
 and month(a.closeddate) = month(EOMONTH (eomonth (DATEADD(MONTH, -1, GETDATE()) )))  
 and a.closeflag = 0  
  
 select * from Final  

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa