Force buckle database title 627 change gender

Given a salary table, as shown below, there are values of M = male and F = female. Swap all F and m values (for example, change all F values to m, and vice versa). Requires only one update statement and no intermediate temporary table.

Note that you must write only one update statement. Do not write any select statements.

For example:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

After running the update statement you wrote, you will get the following table:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

Source: leetcode
The test prefix in SQL is the database name, not an imaginary SQL

Scheme I

UPDATE test.salary SET sex = CASE sex WHEN 'f' THEN 'm' WHEN 'm' THEN 'f' ELSE '' END;

The if method can also be used to obtain m when gender is f and F when gender is m.

Scheme II

UPDATE test.salary SET sex = REPLACE('fm',sex,'');

Programme III

211 is the sum of ASCII encoding of FM

UPDATE test.salary SET sex = CHAR(211-ASCII(sex)USING ASCII);

Programme IV

11 is the exclusive or of ASCII encoding of FM

UPDATE test.salary SET sex = CHAR(11^ASCII(sex) USING ASCII);

Programme V

11118 is the product of ASCII encoding of FM

UPDATE test.salary SET sex = CHAR(11118/ASCII(sex) USING ASCII);

The latter three schemes are basically the same. For development, you can understand one of them

Build table

CREATE TABLE `salary` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(1) NOT NULL DEFAULT '',
  `sex` varchar(1) NOT NULL DEFAULT '',
  `salary` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

Data initialization

INSERT INTO `test`.`salary`(`id`,`name`,`sex`,`salary`)
VALUES(1,'A','m',2500),(2,'B','f',1500),(3,'C','m',5500),(4,'D','f',500);