MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table scores (
  id int,
  name varchar(25),
  datetime_added timestamp,
  `option` varchar(10)
) ;

insert into scores values 

(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');

set @@cte_max_recursion_depth =1000000;
INSERT INTO scores (id, name, datetime_added, `option`)
WITH RECURSIVE cte (n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 100000
)
SELECT n, concat(n,'all'), now(), 'none'
FROM cte;

Records: 7  Duplicates: 0  Warnings: 0



Records: 100000  Duplicates: 0  Warnings: 0


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

Records: 0  Duplicates: 0  Warnings: 0


 explain 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')


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
scores
null
range
opt_nam_id
opt_nam_id
146
null
5
100.00
Using index condition; Using filesort


explain 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; 


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
<derived2>
null
ALL
null
null
null
null
5
20.00
Using where

1
UPDATE
scores
null
ALL
null
null
null
null
100007
10.00
Using where

2
DERIVED
scores
null
range
opt_nam_id
opt_nam_id
146
null
5
100.00
Using where; Using filesort


select * from scores where name ='Rubio';


id
name
datetime_added
option


5
Rubio
2023-01-01 07:05:55
none

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	<derived2>	null	ALL	null	null	null	null	5	20.00	Using where
1	UPDATE	scores	null	ALL	null	null	null	null	100007	10.00	Using where
2	DERIVED	scores	null	range	opt_nam_id	opt_nam_id	146	null	5	100.00	Using where; Using filesort