Join temp table and regular table throwing error

Question

 SELECT * INTO #XmlData FROM
 (
 SELECT d.v.value('(LineItem/text())[1]','VARCHAR(MAX)') AS LineItem,
 d.v.value('(XFundCode/text())[1]','VARCHAR(MAX)') AS XFundCode
 FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)  
 )


 UPDATE lt   
 SET lt.XFundCode = a.XFundCode  
 From TblLineItemTemplate lt INNER JOIN #XmlData a ON lt.LineItem=a.LineItem  
 WHERE lt.TickerID=@TickerID  

also tried this but same error message

UPDATE TblLineItemTemplate
SET TblLineItemTemplate.XFundCode = a.XFundCode
From TblLineItemTemplate lt INNER JOIN #XmlData a ON lt.LineItem=a.LineItem
WHERE lt.TickerID=@TickerID

compilation error is Msg 156, Level 15, State 1, Procedure USP_BulkUpdateXFundCode, Line 55 [Batch Start Line 0]
Incorrect syntax near the keyword 'UPDATE'.

what is wrong there in my code

Answer 1

Try adding something like “as t”: SELECT * INTO #XmlData FROM ( . . . ) as t.

Answer 2

Hi @Sudip Bhatt ,

As mentioned by other experts, we must add an ALIAS on the subquery.

The alias after the subquery (or derived table, if you prefer) is required by SQL Server. It is not only a requirement but a really good idea. In general, column references should be qualified, meaning that they include a table alias. Without an alias, references to columns in the subquery could not be qualified. I think that's a bad thing.

So please refer below query:

 SELECT * INTO #XmlData FROM  
  (  
  SELECT d.v.value('(LineItem/text())[1]','VARCHAR(MAX)') AS LineItem,  
  d.v.value('(XFundCode/text())[1]','VARCHAR(MAX)') AS XFundCode  
  FROM @XmlData.nodes('/Root/TickerTemplate') AS d(v)    
  ) t  
      
      
  UPDATE lt     
  SET lt.XFundCode = a.XFundCode    
  From TblLineItemTemplate lt INNER JOIN #XmlData a ON lt.LineItem=a.LineItem    
  WHERE lt.TickerID=@TickerID  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.