day5 gender change

This question uses MySQL 8.0 and has not been tested in MySQL 5.6, so it is not guaranteed to be correct.

topic

Topic source: Gender change

sex f and m of all data 061f4d0e5de005, use only a single update statement, and do not generate an intermediate temporary table

create table salary (
    id int primary key,
    name varchar(255),
    sex varchar(255),
    salary int
)

insert into salary values
(1, 'A', 'm', 2500),
(2, 'B', 'f', 1500),
(3, 'C', 'm', 5500),
(4, 'D', 'f', 500);

SQL: Method 1

update salary set sex = case sex when 'm' then 'f' else 'm' end;

Parse

If sex = 'm' , then set to f , otherwise set to m .

case sex when 'm' then 'f' else 'm' end can be achieved using 061f4d0e5de0a1

SQL: Method 2

update salary set sex = if(sex = 'm', 'f', 'm');

Parse

The same idea and method

This is using if(sex = 'm', 'f', 'm')

SQL: Method 3

update salary set sex = char(ascii(sex) ^ ascii('m') ^ ascii('f'));

Parse

Each letter corresponds to a ascii , use ascii() to convert the letter to ascii .

In using XOR, the same number is 0 , 0 and any number XOR is that number.

Then use char convert the ascii code into letters

Specific steps:

• ascii('m') is 109 and ascii('f') is 102
• ascii('m') ^ ascii('m') ^ ascii('f') , it is converted into 109 ^ 109 ^ 102 , 109 and 109 exclusive OR is 0 , 0 and 102 exclusive OR is 102 , with char(102) the outcome of m .
• ascii('f') ^ ascii('m') ^ ascii('f') , it is converted into 102 ^ 109 ^ 102 , can be exchanged at 109 and 102 position, so 102 and 102 exclusive OR is 0 , 0 and 109 exclusive OR is 109 , with char(109) the outcome of f .

More reference for solving problems: https://github.com/astak16/blog-mysql