SQL How to update every nth row which meets requirement

I have a table that I would like to update one column data on every nth row if it meets row requirement.
My table has many columns but the key are Object_Id (in case this could be useful for creating temp table)
But the one I’m trying to update is online_status, it looks like below, but on bigger scales so I usually have 10rows that has same time but they all have %Online% in it and in total around 2000 rows (with Online and about another 2000 with Offline). I just need to update every 2-4 rows of those 10 that are repeating itself.
Table picture here: (for some reason table formatting doesn’t come up good)
Table

So what I tried is:
This pulls a list of every 3rd record that matches criteria Online, I just need a way to update it but can’t get through this.

SELECT * FROM (SELECT *, row_number() over() rn FROM people 
WHERE online_status LIKE '%Online%') foo WHERE online_status LIKE '%Online%' AND foo.rn % 3 =0

What I also tried is:
However this has updated every single row. not the ones I needed.

UPDATE people 
SET online_status = 'Offline 00:00-24:00' 
WHERE people.Object_id IN
(SELECT *
FROM 
(SELECT people.Object_id, row_number() over() rn FROM people 
WHERE online_status LIKE '%Online%') foo WHERE people LIKE '%Online%' AND foo.rn % 3 =0);

Is there a way to take list from Select code above and simply update it or run a few scripts that could add it to like temp table and store object ids, and the next script would update main table if object id would match temp table.
Thank you for any help 🙂

Answer

Don’t select other columns but Object_id in the subquery at WHERE people.Object_id IN (..)

UPDATE people 
SET online_status = 'Offline 00:00-24:00' 
WHERE Object_id IN
( SELECT Object_id
  FROM 
  ( SELECT p.Object_id, row_number() over() rn 
    FROM people p
    WHERE p.online_status LIKE '%Online%') foo
  WHERE foo.rn % 3 = 0
 );

Advertisement

Answer