title: Updating Product Order is not working
body: <p>I have Products table with (Name, ParentID, Order) columns. I have a insert statement which keep the inserted child products. After the insert I need to update the order,</p>
  
  <p>I have the following SQL,</p>
  
  <pre><code>UPDATE  Products
  SET     [Order]  = (SELECT ISNULL(MAX([Order]), 0) + 1 FROM Products WHERE ParentID = CP.ParentID) 
  FROM    Products P
          INNER JOIN #InsertedChildProduct CP ON (CP.ID = P.ID) 
  </code></pre>
  
  <p>The problem is that I am updating the order of products that are just inserted, but [Order] is not working. If I have,</p>
  
  <pre><code>Products
  --------
  ParentID  Order
  ----------------
  1          1
  1          2  
  </code></pre>
  
  <p>and let say I have inserted 2 child products then the table should be,</p>
  
  <pre><code>Products
  --------
  ParentID  Order
  ----------------
  1          1
  1          2  
  1          3
  1          4
  </code></pre>
  
  <p>But I am seeing,</p>
  
  <pre><code>Products
  --------
  ParentID  Order
  ----------------
  1          1
  1          2  
  1          3
  1          3
  </code></pre>