Geek Answers Handbook

UPDATE using subqueries - Updates more than required records

Good day to all. I would like to ask a question regarding my SQL statement. I am using SQL Server 2008 and has a Workflow Transaction table. In this table I have 12 entries. The figure below shows the contents of the table.

enter image description here

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 goal is to update a query identifier whose OrderNumber value is less than the maximum value that will be output from the SELECT statement inside the WHERE clause. Now I expect that the updated records should only be the mentioned records (in the code, this is RequestId # 3).

In fact, not only four records were updated, but also five (5)! Is there a problem with my existing SQL operation?

+8
sql sql-server
source share
3 answers

Your problem is that you are updating ALL records with RequestId = 3. Note that the result of your subquery is 3 , so you end up updating all related records.

Your request is equivalent to request

 UPDATE Workflow_Txn SET Status = 1 WHERE RequestId = 3

Not sure if you have a reason to make your request more complex than necessary. It seems to me that something easier will do the trick

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

The problem with your query is that the subquery goes in details to find records with a sequence number less than the maximum. And then he selects everything with the same request - which will include the maximum order number.

I prefer to fix this with 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 CTE separately to see which records are likely to be updated.

To correct your request, you must change the request to use OrderNumber and repeat 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 )
+6
source share

Your subquery said it returns RequestID of 3, so you updated all requests with this ID; go through it. What I think you were looking for was something like:

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

More articles:


All Articles