Erroneous table reference inserted

• 
  Hi @sqlScott2
  
  I'm sorry you're seeing this issue! Thanks for reaching out to us regarding this.
  
  For absolutely clarity, are you saying that SQL Prompt have changed your code, and not just the formatting of it?

  Dan_J December 23, 2022 14:49

  - 0 +
• 
  That is correct. dbo.table1 got changed to inserted in my example.

  sqlScott2 January 03, 2023 14:31

  - 0 +
• 
  Hi @sqlScott2
  
  My apology for the delay in coming back to you on this.
  
  Would it be possible for you to send over your active style file so that I can use it in my testing of this issue? I'm happy to reach out to you directly via a ticket if you would prefer not to provide this via the forum.

  Dan_J January 10, 2023 11:36

  - 0 +
• 
  I am using a new work computer and I had tried to export my SQL Prompt settings on old computer and import them in on my new computer. Did NOT seem to be working. The instruction for that in the forum seemed conflicting with reality. So I am not sure what the right way to do it is.
  On new computer I have SQL Prompt 10.13.3.32087
  On old computer I have 10.13.1.31417
  
  Maybe i need to get newer version on old computer?

  sqlScott2 January 10, 2023 16:43

  - 0 +
• 
  Hi @sqlScott2,
  
  The Export and Import functionality should allow for the bulk of your settings to be migrated across, however you will need to manually copy over your Snippet and Style folders over from:  C:\Users\youruser\AppData\Local\Red Gate\SQL Prompt 10\
  
  Aside from your Snippets and Styles, are there any other settings you've noticed have not migrated over?
  
  As far as I'm aware there should be no issues with doing this with the versions of SQL Prompt you have installed. 
  
  
  

  Dan_J January 19, 2023 09:41

  - 0 +
• 
  Hi @sqlScott2
  
  Thank you for the style file you provided.
  
  Strangely, I am not seeing the same change to the code that you are describing, and so I have asked for our development teams' input on this.
  
  I will come back to you on this again as soon as possible.

  Dan_J January 20, 2023 14:22

  - 0 +
• 
  Hi @sqlScott2
  
  My apology for the delay in coming back to you regarding this.
  
  Unfortunately neither myself or our development team have been able to reproduce the behavior you reported here. Without a working reproduction of the issue here, it's impossible for the team to investigate this or produce a fix, if one is required.
  
  Please do let us know if you notice anything like this happen again so that we can attempt to reproduce it.

  Dan_J February 06, 2023 15:14

  - 0 +
• 

  Thanks for trying. I will let you know if I see a simular problem in the future.

  sqlScott2 February 06, 2023 15:44

  - 0 +
• 

  Dan_J said:

  Hi @sqlScott2,
  
  The Export and Import functionality should allow for the bulk of your settings to be migrated across, however you will need to manually copy over your Snippet and Style folders over from:  C:\Users\youruser\AppData\Local\Red Gate\SQL Prompt 10\
  
  Aside from your Snippets and Styles, are there any other settings you've noticed have not migrated over?
  
  As far as I'm aware there should be no issues with doing this with the versions of SQL Prompt you have installed. 
  
  
  

  How can users ensure a smooth migration of settings, including manually transferring Snippet and Style folders, using SQL Prompt's Export and Import functionality? How can they ensure a seamless transition of data and configurations while considering any relevant mic settings?
  

  wadu June 13, 2024 08:18

  - 0 +
• 

  Thank you for reporting this serious issue with SQL Prompt 10.13.1.31417. This is indeed a critical bug that could have disastrous consequences if undetected.

  Analysis of the Bug

  The formatter is incorrectly changing table aliases in the WHERE clause:

  Original (correct):

  WHERE dbo.table1.key1 = inserted.key1

  After formatting (incorrect):

  WHERE Inserted.key1 = Inserted.key1

  This creates tautologies (Inserted.key1 = Inserted.key1) that would always evaluate to TRUE, causing:

  • The first UPDATE would affect all rows where CurrentStatusFlag = 'Y'
  • The second UPDATE would affect ALL rows in the table
  • Complete data corruption in the trigger logic

  Immediate Recommendations

  1. Report to Redgate: Contact Redgate support immediately with this exact example
  2. Temporary Workaround: Disable automatic formatting for trigger code, or use a different formatting tool
  3. Code Review Process: Ensure all formatted code undergoes thorough review before deployment

  Suggested Report to Redgate

  Subject: CRITICAL BUG: SQL Prompt 10.13.1.31417 corrupts JOIN conditions in UPDATE statements

  Description: The formatter incorrectly replaces table references in WHERE clauses with incorrect aliases, creating tautological conditions that would cause massive data corruption.

  Impact: UPDATE statements in triggers become destructive, affecting wrong rows or entire tables. Reproduction: Format the provided UPDATE statements to see the corruption.

  Alternative Single UPDATE (as you mentioned)

  Since you're consolidating to a single UPDATE, here's a safer approach:

  UPDATE t1 SET CurrentStatusFlag =    CASE WHEN i.key1 IS NOT NULL         THEN 'Y'         ELSE 'N'    END FROM dbo.table1 t1 LEFT JOIN inserted i ON t1.key1 = i.key1    AND t1.key2 = i.key2    AND t1.key3 = i.key3    AND t1.key4 = i.key4    AND t1.key5 = i.key5 WHERE t1.key1 IN (SELECT key1 FROM inserted);

  Thank you for catching this before it reached production. This is exactly why thorough code review processes are essential, even with trusted tools like Michigan County Map.

  Michael Mike October 29, 2025 02:59

  - -1 +

Add comment

Please sign in to leave a comment.