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.