Share via

How to include update stmts with Control expressions?

Remo522 21 Reputation points
2020-08-27T15:18:26.79+00:00

Hello There -

I have a query that selects into a sql statement and then have bunch of updates.. i am trying to use cte but not sure how to add multiple update stmts after the cte. Do we use comma after each Update or? Any help is appreciated.

Is the below sql stmt valid?

with cte1 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()) ))--'5/31/2019'
and a.NewCountFlag = 1),

cte2 as(
update a
set a.CloseFlag = 1
from cte1 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 cte1 l)
and a.closeflag = 0),

cte3 as(
update a
set a.CloseFlag = 1
from cte1 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 cte1 l)
and a.reopeneddate < a.closeddate
and a.closeflag = 0

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,640 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 111.8K Reputation points MVP
    2020-08-27T21:41:01.053+00:00

    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.

    2 people found this answer helpful.
    0 comments
  2. MelissaMa-MSFT 24,201 Reputation points
    2020-08-28T02:46:06.897+00:00

    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

    1 person found this answer helpful.
  3. Jingyang Li 5,891 Reputation points
    2020-08-27T21:51:22.093+00:00

    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.

    0 comments
  4. Erland Sommarskog 111.8K Reputation points MVP
    2020-08-28T22:12:11.37+00:00

    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.

    0 comments
  5. MelissaMa-MSFT 24,201 Reputation points
    2020-09-01T03:21:08.367+00:00

    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

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.