title: Update multiple rows using multiple tables in a join
body: <p>I need to update two rows and I need to use an update. All I have tried has failed.</p>
  
  <p>I got the joins and select statements to work but they do not work in the Update statement.</p>
  
  <pre><code>Select [Order Details].ProductID
  From [Order Details] Inner Join Products
  On [Order Details].ProductID = Products.ProductID
  Inner join Categories
  On Products.CategoryID = Categories.CategoryID
  Where CategoryName = 'Confections' and Unitsinstock &gt; 50
  Group By [Order Details].ProductID
  </code></pre>
  
  <p>This statement found what I needed to update but when I used it, it would not work.</p>
  
  <pre><code>Begin Transaction
  Go
  Update [Order Details]
  Set Discount = (SELECT Max(Discount))
  FROM [Order Details] 
  Right Join Products
      On [Order Details].ProductID = Products.ProductID
      Inner join Categories
          On Products.CategoryID = Categories.CategoryID
          Where CategoryName = 'Confections' and Unitsinstock &gt; 50
  Group By [Order Details].ProductID
  Go
  RollBack Transaction
  </code></pre>
  
  <p>I am using the Roll Back just incase I mess up.
  Any help is appreciated.</p>