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

Omid

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

intGod

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

Dan_J

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

Omid

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.

Omid

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>

*/