how to update unique default option in one sql

Question

i have table like this

-----------------------
id | name | is_default|
------------------------
1  | a    |  1        |
2  | a    |  0        |
3  | a    |  0        |
4  | a    |  0        |
-----------------------

now i want to change line 2(id =2) is_default to 1,and origin line(id =1) id_default to 0 at the same time,like the choose default option in list in UI. 1.can i do this in one sql statement? 2.if it is possible,how to write the sql statement or how to write in mybatis mapper.xml?

Springboot with mybatis ,sql statement write in mapper.xml

@Data
pulbic class Option{
   private Integer id;
   private String name;
   private Boolean isDefault;
}

how to write the mybatis or mysql statement?

Answer 1

You may use a CASE expression:

UPDATE yourTable
SET is_default = CASE WHEN id = 1 THEN 0 ELSE 1 END
WHERE id IN (1, 2);

Or, if you intended to just toggle the default values for id 1 and 2, then try:

UPDATE yourTable
SET is_default = CASE WHEN is_default = 1 THEN 0 ELSE 1 END
WHERE id IN (1, 2);

Answer 2

Tim's answer is fine. If the values are only 0/1, you can simplify it to:

UPDATE t
    SET is_default = 1 - is_default
    WHERE id IN (1, 2);