none
How to use CTE in update statement. RRS feed

  • Question

  • Hi,

    I'm using CTE in a stored procedure and have written an update query in which i want to use its result. But on execution, the sp gives an error saying Invalid Object Name.

    This is my cte design 

    ;with MyCTE as ( some logic )
    
    , AnnoCTE as ( some logic )
    
    
    
    Update <table_name> 
    
    	set 
    
    		<column_name> = <value>
    
    	from AnnoCTE a 
    
    	inner join <table_name> <alias>
    
            where <some_conditon> = <value>

    Is this the correct way of using CTE.


    Monday, May 2, 2016 7:37 AM

Answers

  • Try this:

    DECLARE @someTable TABLE (ID INT IDENTITY, value INT)
    INSERT INTO @someTable (value) VALUES
    (1),(2),(3),(4)
    
    ;WITH existingValues AS (
    SELECT *
      FROM @someTable
    ), newValues AS (
    SELECT 1 AS ID, 2 AS value
    UNION ALL
    SELECT NULL AS ID, 5 AS value
    )
    
    MERGE INTO existingValues t
    USING (SELECT * FROM newValues) s
    ON (t.ID = s.ID)
    WHEN MATCHED THEN
      UPDATE 
       SET value = s.value
    WHEN NOT MATCHED THEN
      INSERT (value)
      VALUES (s.value);
    
    
    SELECT *
      FROM @someTable


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, May 2, 2016 3:54 PM

All replies

  • Note:- Also before this update query i'm using the same CTE in an INSERT statement as well.
    Monday, May 2, 2016 7:40 AM
  • Hi,

    I hate to answer without real query and DDL+DML, but in very fast and first glance your format look ok, if I notice correctly.

    You can see some examples including update from CTE here:
    https://msdn.microsoft.com/en-us/library/ms175972.aspx

    ** A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement. It is inline code which is part of the statement.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, May 2, 2016 7:55 AM
    Moderator
  • create table #t (id int,col int)

    insert into #t values (1,10)

    with cte1
    as
    (
     select 1 as c
    ), cte2
    as
    (
      select 1 id, c+1000 c1 from cte1
    )
    update #t set col=c1 from cte2 join #t on  #t.id=cte2.id 

    select * from #t
    1 1001

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 2, 2016 8:08 AM
    Answerer
  • Please post the actual code. In SQL Server 2008 and up you can use MERGE command to perform update and insert as one operation.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, May 2, 2016 12:11 PM
    Moderator
  • Hello Harshul,

    The main reason for you getting the error in the UPDATE statement is you are using the same CTE in the INSERT statement just before executing the UPDATE statement.

    Actually, a CTE has the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

    For more information, you can refer this msdn link.

    Hope, this helps!


    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia

    Monday, May 2, 2016 12:57 PM
  • Please find updated code below
     create table #tempTable (_id int)
    
    ;with MyCTE as ( some code)
    , newCte as ( some code)
    
    	insert #tempTable
    	select location_parent_id 
    
    from newCte 
    update 
     --code
    
    insert 
     -- code
    
    and not exists (code)
    
    this is giving me error still now that is some variable type is not matching
    
    

    Monday, May 2, 2016 1:04 PM
  • create table #tempTable (_id int)
    
    ;with MyCTE as ( some code)
    , newCte as ( some code)
    
    	insert #tempTable
    	select location_parent_id 
    
    from newCte ;

    If need to start over for your update section.

    But you can use merge to process all these together.

    You can find an example from this link:

    https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396


    Monday, May 2, 2016 1:10 PM
    Moderator
  • ;with MyCTE as ( some logic )
    
    , AnnoCTE as ( some logic )
    
    Update <alias> -- It should be the table alias here
    
    	set 
    
    		<column_name> = <value>
    
    	from AnnoCTE a 
    
    	inner join <table_name> <alias>
    
            where <some_conditon> = <value>

    Monday, May 2, 2016 3:04 PM
  • Try this:

    DECLARE @someTable TABLE (ID INT IDENTITY, value INT)
    INSERT INTO @someTable (value) VALUES
    (1),(2),(3),(4)
    
    ;WITH existingValues AS (
    SELECT *
      FROM @someTable
    ), newValues AS (
    SELECT 1 AS ID, 2 AS value
    UNION ALL
    SELECT NULL AS ID, 5 AS value
    )
    
    MERGE INTO existingValues t
    USING (SELECT * FROM newValues) s
    ON (t.ID = s.ID)
    WHEN MATCHED THEN
      UPDATE 
       SET value = s.value
    WHEN NOT MATCHED THEN
      INSERT (value)
      VALUES (s.value);
    
    
    SELECT *
      FROM @someTable


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Monday, May 2, 2016 3:54 PM