How to include update stmts with Control expressions?

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

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

{count} votes

5 answers

Sort by: Most helpful
  1. answered 2020-08-27T21:41:01.053+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    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.

    No comments

  2. answered 2020-08-28T02:46:06.897+00:00
    MelissaMa-MSFT 24,116 Reputation points

    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


  3. answered 2020-08-27T21:51:22.093+00:00
    Jingyang Li 4,371 Reputation points

    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.

    No comments

  4. answered 2020-08-28T22:12:11.37+00:00
    Erland Sommarskog 67,636 Reputation points Microsoft MVP

    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.

    No comments

  5. answered 2020-09-01T03:21:08.367+00:00
    MelissaMa-MSFT 24,116 Reputation points

    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

    No comments