MySQL WHEN, THEN, END

[Jabop]

Was reading the tutorial provided by Crayon Violent on the home page and came across this query:

 

$sql = "UPDATE info SET usort = CASE usort WHEN $id THEN $swap WHEN $swap THEN $id END WHERE usort IN ($id, $swap)";

 

Without getting into the code, I know what it's supposed to do in a pseudo-code manner, and reading his comments in context. However, could CV or someone break down what this strange looking query does?

[corbin]

UPDATE info SET usort = CASE usort WHEN x THEN y WHEN y THEN x END WHERE usort IN (x, y)

 

(Replaced with simple vars for simplicity.)

 

 

Think of it this way:

 

UPDATE info SET usort = value WHERE usort IN (x, y);

 

Value could be thought of like this:

 

if(usort == x) value = y;

if(usort == y) value = x;

[.josh]

Let's break it down...

 

QueryA

update table set column = 1 where column = 2

wherever column equals 2, set it to 1

 

Simple enough, right? Okay let's add on the IN(...)

 

QueryB

update table set column = 1 where column in(1,2)

wherever column equals 1 or 2, set it to 1

 

Using IN(...) is the same as doing two QueryA's...

update table set column = 1 where column = 1

update table set column = 1 where column = 2

 

Well that obviously won't work all by itself.  First query is just pointless, and the 2nd query only switches one number. We need to somehow make column = 2 in the first query.  So let's throw the case into the mix:

 

update table set column = case column when 1 then 2 when 2 then 1 end where column in (1,2)

 

CASE column

  WHEN 1 THEN 2

  WHEN 2 THEN 1

END

 

If column equals one, then set it to two.  If it's two, then set it to one.  So that's (kind of) the same as doing this:

update table set column = 1 where column = 2

update table set column = 2 where column = 1

 

Only you can't write it in two queries like that, because if you have for instance this:

 

Column

1

2

 

When you run the first query, you get this:

 

Column

1

1

 

Then when you run the second query, you end up with this:

 

Column

2

2

 

Which is not what we wanted at all.  That's where the magic trick comes in with combining the two queries into one query, using a combination of a case...end and in(..).  We exploit the database's affinity for making temporary variables to keep stuff sorted out.  When the "first query" is executed (the query will run once for each variable in the in(...) ):

 

update table set column = 1 where column = 2

 

we do indeed turn

 

Column

1

2

 

into

 

Column

1

1

 

But since the query isn't done yet, it created temporary vars (possibly a whole temporary table.  You never can tell for sure what goes on inside the black hat...).  So when it gets to the second number in the in(...), it is still basing the "second query":

 

update table set column = 2 where column = 1

 

off of the original, unaltered data (the temp vars/table/rabbit keeping track of stuff on his fingers), so we wind up going from

 

Column

1

1

 

(what we got from first iteration) to

 

Column

2

1

 

for that 2nd iteration.  Understand that the actual table is being altered, each time the query is executed (for each value in the IN(...) ); it's just using this temp vars/table as a reference, to keep track of stuff.  That's why if you index the column as something like auto_increment or primary_key or something that demands unique row data, it will scream at you about having duplicate data.