Join temp table and regular table throwing error

Question

Join temp table and regular table throwing error

Sudip Bhatt 2,281

 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

Olaf Helper 47,616 Reputation points

2020-09-09T13:02:15.84+00:00

The error mention code line 55; is that the SQL starting on that line? Because your UPDATE statement looks fine.
Yitzhak Khabinsky 27,106 Reputation points

2020-09-09T13:04:46.79+00:00

Hi @Sudip Bhatt ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;)
Sudip Bhatt 2,281 Reputation points

2020-09-10T06:51:46.167+00:00

Sir if you see my code then surely you understand that i am inserting data into temp table called #XmlData and later join this temp table with a regular table then i encounter error which i have posted here. what more i can mention here. thanks

2 answers

Your answer

Olaf Helper 47,616 Reputation points

2020-09-09T13:02:15.84+00:00

The error mention code line 55; is that the SQL starting on that line? Because your UPDATE statement looks fine.
Yitzhak Khabinsky 27,106 Reputation points

2020-09-09T13:04:46.79+00:00

Hi @Sudip Bhatt ,

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;)
Sudip Bhatt 2,281 Reputation points

2020-09-10T06:51:46.167+00:00

Sir if you see my code then surely you understand that i am inserting data into temp table called #XmlData and later join this temp table with a regular table then i encounter error which i have posted here. what more i can mention here. thanks

Answer 1

MelissaMa-msft 24,246 Moderator

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.

Sudip Bhatt 2,281 Reputation points

2020-09-10T06:39:57.127+00:00
I am not sure i guess there is problem in update statement. please see my update and tell me update syntax is fine ?

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

lt is alias but table name is TblLineItemTemplate. so tell me syntax is right ? thanks
MelissaMa-msft 24,246 Reputation points Moderator

2020-09-10T06:50:24.65+00:00
Hi @Sudip Bhatt ,

Your update statement looks fine.

Please provide the error message if exists after running 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

If you still face any error about it, it is better for you to provide all the details like create table DDL, declare variable statements, some sample data ,error details and expected output so that we could check accordingly.

Best regards
Melissa
Sudip Bhatt 2,281 Reputation points

2020-09-10T08:46:59.977+00:00

Thanks will let you know.
MelissaMa-msft 24,246 Reputation points Moderator

2020-09-11T04:33:26.47+00:00

Hi @Sudip Bhatt ,

Could you please provide any update?Thanks.

Best regards
Melissa

Answer 2

Viorel 126.9K

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

Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2020-09-09T21:29:49.017+00:00

To clarify Viorel's answer (which is correct), When you say:

SELECT .. FROM (subquery)

You must provide an alias for the derived table (which is the formatl name for the subquery), even if you never user that alias. Thus, as Viorel says, it has to be something like:

SELECT .. FROM (subquery) AS x

Share via

Join temp table and regular table throwing error

2 answers

Your answer