New training course: Customizing SQL Prompt. Watch now.

an important issue is ignored, and no warning for that (regarding UPDATE statements)

OmidOmid Posts: 8 New member
edited November 27, 2020 12:41PM in SQL Prompt

Hi,

I noticed that in my codes in a trigger I had a mistake that can be an issue which RedGate SQL Prompt could normally generate warning for that: 

<code>

WITH Tree AS

                                (

                                                 here I make a tree,  without problem

                                )

                                UPDATE               dbo.whs_Inventory

                                SET                         ProfileId                               = NULL

                                FROM   Tree

                                WHERE   Tree.ParentId IS NOT NULL 

 <code/>

 

This Update statement made a disaster. because I forgot to join the tree to the table whs_Inventory which was being updated.

In other words, the table to be updated must be mentioned in FROM clause.

 

The correct sentence is :

                                FROM   dbo.whs_Inventory inv INNER JOIN         

                                                                Tree  ON Tree.InventoryId = inv.InventoryId

                                WHERE   Tree.ParentId IS NOT NULL 

 

Please add this to SQL Prompt, so that users receive relevant warning in such cases.

 

Thank you

Answers

  • intGodintGod Posts: 4 New member
    Best practice to only use a TABLE ALIAS in the UPDATE statement so you will always have to have it explicitly in the FROM statement
  • Hi @Omid,

    Thanks for reaching out to us regarding this and for bring this to our attention.

    I'd like to try and replicate the issue you are seeing to better understand the issue and then potentially escalate it to our development team. Would it be possible to get a copy of your syntax around the following?:


    WITH Tree AS
    (
    here I make a tree, without problem
    )
    UPDATE dbo.whs_Inventory
    SET ProfileId = NULL
    FROM Tree
    WHERE Tree.ParentId IS NOT NULL

    Kind regards

    Dan Jary | Redgate Software
    Have you visited our Help Center?
  • OmidOmid Posts: 8 New member
    intGod said:
    Best practice to only use a TABLE ALIAS in the UPDATE statement so you will always have to have it explicitly in the FROM statement
    Thanks for your advice.
    But as you know SQL Prompt is prode of  his advices and detecting potencial programers mistakes. 
    Here we have a case that worth for seriouse warning to the programer.
    I am wondernig why Microsoft has not covered the issue.
    As you surely have noticed if you run an UPDATE command without WHERE clause, SSMS shows a warning before runing it.
    My issue is similar to that condition,  UPDATE  all records  without any warnings.

  • OmidOmid Posts: 8 New member
    Dan_J said:

    Hi @Omid,

    Thanks for reaching out to us regarding this and for bring this to our attention.

    I'd like to try and replicate the issue you are seeing to better understand the issue and then potentially escalate it to our development team. Would it be possible to get a copy of your syntax around the following?:


    WITH Tree AS
    (
    here I make a tree, without problem
    )
    UPDATE dbo.whs_Inventory
    SET ProfileId = NULL
    FROM Tree
    WHERE Tree.ParentId IS NOT NULL

    Hi
    Thanks for your comment
    my complete code was:
    <div>WITH Tree AS&nbsp;</div><div> (	SELECT	inv.InventoryId, inv.ParentId, inv.ProfileId&nbsp;</div><div>    FROM dbo.whs_Inventory inv INNER JOIN	</div><div>         dbo.whs_ReceiptDetail RD ON RD.QRcode = inv.QRcode INNER JOIN</div><div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inserted ins ON&nbsp; ins.ReceiptMasterId = RD.ReceiptMasterId INNER JOIN&nbsp;</div><div>	 Deleted del ON del.ReceiptMasterId = ins.ReceiptMasterId</div><div>   WHERE ins.ReceiptConfirm =1 AND del.ReceiptConfirm=0</div><div>   UNION ALL</div><div>      SELECT child.InventoryId , child.ParentId, child.ProfileId&nbsp;<br></div><div>   FROM dbo.whs_Inventory child INNER JOIN
           Tree ON child.ParentId = Tree.InventoryId</div><div>)</div><div>UPDATE	dbo.whs_Inventory</div><div>SET	ProfileId	= NULL,</div><div>	DeliveryDate	= NULL</div><div>FROM	Tree&nbsp; </div><div>WHERE&nbsp; ParentId IS NOT NULL
    
    
    /* -- Correct From cluase is :
    <pre class="CodeBlock"><code><div>FROM	dbo.whs_Inventory inv INNER JOIN	<br></div><div>        Tree ON Tree.InventoryId = inv.InventoryId</div><div>WHERE&nbsp; &nbsp;Tree.ParentId IS NOT NULL</div>
    */
Sign In or Register to comment.