By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table example_table(
pk int auto_increment primary key
, textfield char(1)
, do_not_touch boolean
)
EXPLAIN format=tree
UPDATE example_table
SET textfield = 'X'
WHERE textfield = 'Y'
and pk not in (SELECT pk FROM (SELECT pk FROM example_table WHERE do_not_touch =1)as t) ;
| EXPLAIN |
|---|
| -> Update example_table (immediate) -> Nested loop antijoin -> Filter: (example_table.textfield = 'Y') (cost=0.35 rows=1) -> Table scan on example_table (cost=0.35 rows=1) -> Index lookup on t using <auto_key0> (pk=example_table.pk) -> Materialize -> Filter: (example_table.do_not_touch = 1) (cost=0.35 rows=1) -> Table scan on example_table (cost=0.35 rows=1) |
EXPLAIN
UPDATE example_table
SET textfield = 'X'
WHERE textfield = 'Y'
and do_not_touch !=1
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | UPDATE | example_table | null | index | null | PRIMARY | 4 | null | 1 | 100.00 | Using where |