Given a single table, swapping two columns within that table is quite easy:
UPDATE table SET a = b, b = a;
If instead, we want to swap one field between two rows, for example, the priority of two rows in a prioritized list implemented in the database, the query requires some algebraic tricks:
UPDATE table SET priority = A + ABS(priority - B) WHERE priority in (A , B);
where A and B are integer constants such that A < B. This is an interesting interview problem, primarily because the first instinct is to use a
CASE WHEN statement. This problem also arises commonly in many database applications, such as shopping carts and queues.