an important issue is ignored, and no warning for that (regarding UPDATE statements)
Omid
Posts: 8 New member
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
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
Dan Jary | Redgate Software
Have you visited our Help Center?
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.
Thanks for your comment
my complete code was:
<div>WITH Tree AS </div><div> ( SELECT inv.InventoryId, inv.ParentId, inv.ProfileId </div><div> FROM dbo.whs_Inventory inv INNER JOIN </div><div> dbo.whs_ReceiptDetail RD ON RD.QRcode = inv.QRcode INNER JOIN</div><div> Inserted ins ON ins.ReceiptMasterId = RD.ReceiptMasterId INNER JOIN </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 <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 </div><div>WHERE 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 Tree.ParentId IS NOT NULL</div>*/