update query with table alias confuses SQL Prompt

• 
  Hi,
  
  Good spot! Thanks for reporting this. I've added an issue to our system.
  
  Regards,
  
  Tilman

  Tilman December 20, 2006 10:57

  - 0 +
• 
  i have the latest build (from early january) and i think this had not been dealt with in that version. is there an ETA for a correction?

  jjderoo January 16, 2007 16:45

  - 0 +
• 
  Hi gents,
  
  
  Well, here's what I get with the following on AdventureWorks on SQL Server 2005 in our current release candidate (which hasn't been made public):
  
  UPDATE [Production].[Product]
  SET
  FROM [Production].[Product] AS p
  JOIN [Sales].[ShoppingCartItem] AS sci
  ON [p].[ProductID] = [sci].[ProductID]
  WHERE sci.[Quantity] > 1
  
  (^ indicates the caret position)
  
  The "Suggested candidates" list contains the following items:
  
  (i) "p"
  (i) "sci"
  (iii) All columns from "p" and "sci"
  (iv) other database objects
  
  Clearly this isn't correct at this point, but as soon as I enter "p" I get the expected list:
  
  (i) Product table
  (ii) Columns in Product beginning with "p".
  (iii) etc
  
  I agree that this isn't ideal, however it's rather hard for our parser to deal with an UPDATE statement that's broken in this way and it's error recovery finds it difficult to distinguish between an incomplete UPDATE statement and an incomplete SET variable/option statement. I do agree that this could be better though, but given that it starts behaving once you've entered a character it's something we've decided to leave for now. We will look into this again for the future though.
  
  Sorry I can't be more immediate help.
  
  
  Thanks,
  Bart
  
  [/img]

  Bart Read January 17, 2007 12:47

  - 0 +
• 
  I should just clarify by saying that if you do the following:
  
  UPDATE [Production].[Product]
  SET ^
  
  you get the correct list with Product and its columns, but the FROM clause appears to screw up the lookahead in some way until you've entered another token after the SET. Once you've entered the SET clause you can carry on and enter your FROM clause and it should behave a lot better.
  
  Hope that helps.
  
  
  Thanks,
  Bart

  Bart Read January 17, 2007 13:11

  - 0 +
• 
  Hi,
  
  
  I must apologise because there was one crucial difference between your query and my query that I failed to spot (it was getting late in the day yesterday ), which is that you were trying to update using the alias.
  
  So, here's what I get when I try the following:
  
  UPDATE p
  SET ^
  FROM [Production].[Product] AS p
  JOIN [Sales].[ShoppingCartItem] AS sci
  ON [p].[ProductID] = [sci].[ProductID]
  WHERE sci.[Quantity] > 1
  
  In the candidate list I get:
  
  (i) "p"
  (ii) "sci" (strictly speaking shouldn't be there)
  (iii) Columns from "p" and "sci" (again, strictly speaking the latter shouldn't be here, although you could reference them as part of the expression used to set a column value but that would obviously be after the '=').
  (iv) Other objects which can be used in the expressions defining the values to be assigned to each column.
  
  As soon as you enter a "p" all you get is "p" and the columns belonging to it that start with "p". Qualifying by "p" (i.e. "p.") gives you just the columns in p.
  
  This is much closer to the desired behaviour for this situation I think. Thanks for the feedback, and I hope this helps you out and is an improvement over what you've been experiencing in the previous releases.
  
  
  Kind regards,
  Bart

  Bart Read January 18, 2007 07:12

  - 0 +

Add comment

Please sign in to leave a comment.