SQL Exercise – How to Swap Columns? (MySQL)

Let’s suppose we have a table containing two columns ID and sex, and our task is to swap the values of sex i.e. from ‘f’ to ‘m’ and from ‘m’ to ‘f’. For instance,

| id | sex 
|----|-----
| 1  | m   |
| 2  | f   |
| 3  | m   | 
| 4  | f   | 

after running the query, the table should become:

| id | sex 
|----|-----
| 1  | f   |
| 2  | m   |
| 3  | f   | 
| 4  | m   | 

And what are the most efficient ways to do this, without any intermediate temp table?

Using Case/If in MySQL to swap columns

MySQL supports case statement, and this becomes useful if you have multiple cases:

1
2
3
4
5

UPDATE salary
    SET sex  = (CASE WHEN sex = 'm' 
        THEN  'f' 
        ELSE 'm' 
        END)

UPDATE salary
    SET sex  = (CASE WHEN sex = 'm' 
        THEN  'f' 
        ELSE 'm' 
        END)

Since this has only two possible cases, we can simplify the query using the IF function (similar to IFThen in Excel).

1

UPDATE salary SET sex = IF(sex='m','f','m');

UPDATE salary SET sex = IF(sex='m','f','m');

Using XOR to swap values

The exclusive or i.e. A^B^A=B, B^A^B=A, any number XOR twice will become zeros. 0 XOR 1 = 1, therefore, we can swap two-cases values easily by XOR:

1

update salary set sex = CHAR(ASCII('f') ^ ASCII('m') ^ ASCII(sex));

update salary set sex = CHAR(ASCII('f') ^ ASCII('m') ^ ASCII(sex));

The ASCII of ‘f’ is 0x66 and ‘m’ is 0x6d, so the XOR value will become 0x0B (11 in decimal), so this becomes simplified to:

1
2

update salary
set sex = char(ascii(sex) ^ 11);

update salary
set sex = char(ascii(sex) ^ 11);

–EOF (The Ultimate Computing & Technology Blog) —