SQL switching values on a column In interviews for software developer roles SQL questions can sometimes come up. There is a classic question around switching values in a column, typically swapping the gender of people in a table for example:

id   name   sex   salary
------------------------
0    Tom    f     45000
1    Jane   m     22000


the typical question asks you to do this in one statement (and the answer is here: Switching values in a column with one update statement)

However my question is NOT that.

My question is what is the naive approach, that is, how I can temporarily store the values. In fact I'd like to do this two ways: 1. In a temporary column (that does not persist after the statement) and 2. In a temporary variable.
Odd question with terms like temporary storage. General SQL is denormalized so fetching when need rather than a transform.

Question 1: results and be an expression like:

SELECT id, sex, IF(sex='m', 'f', 'm') as antisex FROM table


Question 2: A variable can contain only a single value. So from ref manual on SELECT INTO:

SELECT id, sex, IF(sex='m', 'f', 'm') INTO @id, @sex, @antisex FROM table LIMIT 1