Updating row withing select depending on result of union select

I have a table called “scores” with following info:

| id | name  | datetime_added      | option |
+----+-------+---------------------+--------+
| 1  | all   | 2023-01-01 00:10:04 | none   |
+----+-------+---------------------+--------+
| 2  | James | 2023-01-01 00:32:18 | none   |
+----+-------+---------------------+--------+
| 3  | all   | 2023-01-01 02:08:51 | none   |
+----+-------+---------------------+--------+
| 4  | all   | 2023-01-01 04:13:07 | none   |
+----+-------+---------------------+--------+
| 5  | Rubio | 2023-01-01 07:05:55 | none   |
+----+-------+---------------------+--------+
| 6  | all   | 2023-01-01 09:17:27 | none   |
+----+-------+---------------------+--------+
| 7  | James | 2023-01-01 10:43:01 | none   |
+----+-------+---------------------+--------+

For output I need to add option “some” to the newest entry of “Rubio” where option is “none” if there is any. If there isn’t (either name “Rubio” or option “none”) then I need to add the option “some” to the newest entry of “all”.

What I have done so far? I need to get that table sorted in two ways:

1. By name, “Rubio” needs to be first and “all” needs to be second (names can change and there can be more of them, so alphabetical is not helpful here but 1 name out of all needs to come first, then all and rest ignored).
2. By datetime_added (ID is auto increment so sorting by id should do the trick)

I can manage this by doing:

(SELECT * FROM scores WHERE name = "Rubio" AND option = "none" ORDER BY id DESC)
UNION
(SELECT * FROM scores WHERE name = "all" AND option = "none" ORDER BY id DESC);

This giving me necessary list of:

| id | name  | datetime_added      | option |
+----+-------+---------------------+--------+
| 5  | Rubio | 2023-01-01 07:05:55 | none   |
+----+-------+---------------------+--------+
| 1  | all   | 2023-01-01 00:10:04 | none   |
+----+-------+---------------------+--------+
| 3  | all   | 2023-01-01 02:08:51 | none   |
+----+-------+---------------------+--------+
| 4  | all   | 2023-01-01 04:13:07 | none   |
+----+-------+---------------------+--------+
| 6  | all   | 2023-01-01 09:17:27 | none   |
+----+-------+---------------------+--------+

How could I update the table within this one query now when I know I need to:

UPDATE scores SET option = "some" WHERE id = 5;

This request is also run up to once per second, is this efficient?

For output I need to add option “some” to the newest entry of “Rubio”
where option is “none” if there is any. If there isn’t (either name
“Rubio” or option “none”) then I need to add the option “some” to the
newest entry of “all”.

This can be done by a case statement in the order by clause ,

 order by case 
            when name="Rubio" and `option`='none' then 1 
            when name="all" and `option`='none' then 2 
            else 3 end  asc, datetime_added desc ) row_num

Use row_number window function to give update the value

WITH ToUpdate AS (
   select id,
          row_number() over( order by case when name="Rubio" and `option`='none' then 1 
                                           when name="all" and `option`='none' then 2 
                                           else 3 end  asc,datetime_added desc ) row_num
  from scores
  where `option`='none' 
  and name in ('all','Rubio')
)   
UPDATE scores  
INNER JOIN  ToUpdate  ON scores.id = ToUpdate.id
SET  `option` = 'some' 
WHERE ToUpdate.row_num = 1; 

Above query will update only if name="Rubio" and option='none' or name="all" and option='none' otherwise would do nothing because other records are filtered in the where condition where option='none' and name in ('all','Rubio')

See example

Edit

How efficient do you think this method is? Considering I would need to
run that query around twice per second on peak times against a table
with more than 100 000 rows?

To speed up the query add the following index

alter table scores add index opt_nam_id(`option`,name,id);

See example

I cant be sure how fast the query would be on your environment, please make the suggested changes and let me know.

Don’t use UNION, use LEFT JOIN. Then you can use IFNULL() to provide one selection as a default if the other selection isn’t found.

UPDATE scores AS s
JOIN (
  SELECT IFNULL(t1.id, t2.id) AS id
  FROM (SELECT id
        FROM scores
        WHERE name="Rubio" AND `option` = 'none'
        ORDER BY id DESC
        LIMIT 1) AS t1
  LEFT JOIN (SELECT id
        FROM scores
        WHERE name="all" AND `option` = 'none'
        ORDER BY id DESC
        LIMIT 1) AS t2 ON 1 = 1
) AS x ON s.id = x.id
SET s.option = 'some'

DEMO