CODEWITHSUNDEEP
sqlsql-server
Jinmo Chong
Jinmo Chong

Reputation: 121

Query update except latest data on same conditions

I have a scenario to update the rows within the same condition(status = 1) but not the latest row. So this is the table design.

--------------------------------------------------
|idx     | status | var1  |  date
--------------------------------------------------
| 2      |  1     |  cat  | 2018-06-17 15:41:32.110      
| 3      |  1     |  dog  | 2018-06-17 11:41:32.110
| 2      |  1     |  lamb | 2018-06-17 11:41:32.110
| 2      |  1     |  pc   | 2018-06-17 09:41:32.110
| 3      |  1     |  doll | 2018-06-17 09:41:32.110

What I want is to get all the same conditions where idx is equal and status = 1, and update the status to 0 except the most recent row. In this case, there are 3 rows which have idx of 2 and status = 1, and 2 rows which have idx of 3 and status = 1. After the query, the table should look like this

--------------------------------------------------
|idx     | status | var1  |  date
--------------------------------------------------
| 2      |  1     |  cat  | 2018-06-17 15:41:32.110      
| 3      |  1     |  dog  | 2018-06-17 11:41:32.110
| 2      |  0     |  lamb | 2018-06-17 11:41:32.110
| 2      |  0     |  pc   | 2018-06-17 09:41:32.110
| 3      |  0     |  doll | 2018-06-17 09:41:32.110

I have no idea how to do this and tried to at least display the rows which has more than 1 equal conditions and came up with this query

select Idx, status, COUNT(Idx) as count from table 
group by Idx, status
having COUNT(Idx) > 1 and status = 1
order by Idx

This shows how many rows I have in the same condition, but I would also like to have rows to display var1 and date but I don't know how to do that.

As I am working in a .Net development, I could make a list of idx to a list and do a for loop on each idx and update in that for loop, but I would love to learn more about sql, how to solve this through.

Upvotes: 0

Views: 52

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28206

You can also achieve it without the CTE:

UPDATE t SET status = 0 FROM tbl t WHERE NOT EXISTS 
( SELECT 1 FROM tbl GROUP BY idx HAVING MAX(date)=t.date AND idx=t.idx );

see here: http://rextester.com/BVAS22315

The difference between Tim's and my solution would be that in case of two records with the same idx having exactly the same date, Tim's command would leave only one record unchanged (status=1) while my command would keep them both unchanged.

And, using the window function ROW_NUMBER(), you can also do it like this:

UPDATE t SET status=0 FROM 
  (SELECT *, ROW_NUMBER() OVER (PARTITION BY idx ORDER BY date DESC) rn 
   FROM tbl) t 
WHERE rn>1

This second version will behave exactly like Tim's solution, see here: http://rextester.com/MFRAMR93418
(Note the identical dates for 'dog' and 'lamb' and only one gets updated.)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

We can try updating with a CTE:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY idx ORDER BY date DESC) rn
    FROM yourTable
)

UPDATE cte
SET status = 0
WHERE rn > 1 AND status = 1;

Upvotes: 4

Related Questions