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

• 
  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

  intGod November 30, 2020 08:24

  - 0 +
• 

  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
  

  
  

  Dan_J November 30, 2020 15:55

  - 0 +
• 

  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 November 30, 2020 18:36

  - 0 +
• 

  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>

  */

  Omid December 07, 2020 07:09

  - 0 +

Add comment

Please sign in to leave a comment.