Need help writing query to inactivate all except the most recent record

Question

Post reply

Need help writing query to inactivate all except the most recent record

Goalie35

SSC Eights!

Points: 871
More actions
February 19, 2020 at 2:38 pm
Go to Answer
#3727086

In the following table, there can be multiple records per order (indicated by OrderID). There is also a bit Active flag field on each record, which are all currently set to true. So all records are active & I now need to go in and deactivate all old records for all orders EXCEPT the most recent one (I would think evaluating either the OrderDetailID or CreatedDate should both suffice to determine the most recent). What's the best way to do this?
Here's the relevant fields on my table:
Table Name: OrderDetails
OrderDetailsID | OrderID | Active | CreatedDate
123 | 111 | 1 | 2020-01-01
124 | 111 | 1 | 2020-02-02
125 | 111 | 1 | 2020-02-15
126 | 222 | 1 | 2020-01-15
127 | 222 | 1 | 2020-02-05
128 | 333 | 1 | 2020-01-20
In the above table, I would like to set the Active field to zero for all records except the most recent record for each OrderID. So after the update, the table should look like this:
OrderDetailsID | OrderID | Active | CreatedDate
123 | 111 | 0 | 2020-01-01
124 | 111 | 0 | 2020-02-02
125 | 111 | 1 | 2020-02-15
126 | 222 | 0 | 2020-01-15
127 | 222 | 1 | 2020-02-05
128 | 333 | 1 | 2020-01-20
What's the best way to do this?
Thanks
Mark Cowne

One Orange Chip

Points: 26944
More actions
February 19, 2020 at 3:02 pm
Answer
#3727093
```
WITH CTE AS (
SELECT OrderDetailsID, OrderID, Active, CreatedDate,
       ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderDetailsID DESC) AS rn
FROM OrderDetails
)
UPDATE CTE
   SET Active = 0
WHERE Active = 1 AND rn > 1;
```
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 1

What did you test ?

logically step-1 determine the max CreatedDate per orderid

logically step-2 update all rows for the given orders that do not have CreatedDate equal to the max_CreatedDate for that given orderid

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

Mark Cowne wrote:

WITH CTE AS (
SELECT OrderDetailsID, OrderID, Active, CreatedDate,
       ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderDetailsID DESC) AS rn
FROM OrderDetails
)
UPDATE CTE
   SET Active = 0
WHERE Active = 1 AND rn > 1;

You may care to move the WHERE Active=1 to inside the CTE. as in (reusing Mark's as a reference) -

WITH CTE AS (
SELECT OrderDetailsID, OrderID, Active, CreatedDate,
       ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderDetailsID DESC) AS rn
FROM OrderDetails
WHERE Active = 1 
)
UPDATE CTE
   SET Active = 0
WHERE rn > 1;

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?