UPDATE using subqueries - Updates more than the needed records

Question

Good day everyone. I would like to ask a question regarding my SQL statement. I am using SQL Server 2008 and has a table called Workflow Transaction. In this table, I have 12 records. The picture below is the content of the table.

I have this SQL statement:

UPDATE Workflow_Txn 
SET Status = 1
WHERE [RequestId] IN
(
    SELECT [RequestId]
    FROM Workflow_Txn 
    WHERE [OrderNumber] < (SELECT MAX(OrderNumber) FROM Workflow_Txn WHERE RequestId = 3)      
    AND RequestId = 3
)

My objective is to update a request ID that has the OrderNumber of less than the maximum, which will be the output from the SELECT statement inside the WHERE clause. Now I expect that the records to be updated shall be only the said records (in the code, it's RequestId # 3).

What actually happened was instead of only four records being updated, it becomes five (5)! Is there a problem with my existing SQL statement?

Answer 1

Your problem is that you're doing an update of ALL records with RequestId = 3. Take into account that your subquery result is 3 so you end up updating all related records.

Your query is equivalent to do

UPDATE Workflow_Txn 
SET Status = 1
WHERE RequestId = 3

Not sure if you have any reason to make your query more complex than it needs to be. Seems to me that something simpler would do the trick

UPDATE Workflow_Txn 
SET    Status = 1
WHERE  [OrderNumber] < (SELECT MAX(OrderNumber) FROM Workflow_Txn WHERE RequestId = 3)      
       AND RequestId = 3

Answer 2

The problem with your query is that the subquery goes into great detail to find the records with order number less than the maximum. And then it chooses everything with the same request -- which would include the maximum order number.

I prefer to fix this using a CTE as follows:

with toupdate as (
      select t.*,
             MAX(OrderNumber) as MaxON
      from Workflow_txn
      where RequestId = 3
     )
UPDATE toupdate
    SET Status = 1
    where OrderNumber < MaxON;

I like this structure, because I can run the CTE separately to see what records are likely to be updated.

To fix your query, you would change the request to using OrderNumber and repeat the RequestId = 3:

UPDATE Workflow_Txn 
SET Status = 1
WHERE [RequestId] = 3 and
      OrderNumber in
(
    SELECT [OrderNumber]
    FROM Workflow_Txn 
    WHERE [OrderNumber] < (SELECT MAX(OrderNumber) FROM Workflow_Txn WHERE RequestId = 3)      
    AND RequestId = 3
)