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.

  • Are there only 2 rows? – Rick James Jun 13 at 16:36
  • It's an example, rather that a real table. So n rows. – stevenpcurtis Jun 14 at 2:24
  • If N rows, swap just in rows 0 and 1? Or in pairs? If so, what defines a 'pair'? That's just the beginning of my questions to point out how imprecisely phrased the question is. – Rick James Jun 14 at 6:45

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
  • Yes, apologies but as explained I'm learning SQL as it comes up in other interviews rather than being any sort of expert. – stevenpcurtis Jun 13 at 4:55

The common update variant uses 2 copies of a table - one is a data source and another is new values acceptor.

UPDATE table t_src, table t_dst
SET t_dst.field_dst = t_src.field_src
WHERE (compilance conditions)

For the task above (switch sex for the records with IDs 0 and 1) it can be

UPDATE table t_src, table t_dst
SET t_dst.sex = t_src.sex
WHERE (t_src.id, t_dst.id) IN ((0,1), (1,0))

And no "temporarily store the values" needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.