Introduction

Swapping two column values in MySQL sounds simple, but a naive UPDATE can produce the wrong result. The reason is assignment order: in a single-table UPDATE, later assignments may see values that were already changed earlier in the same statement. A safe swap therefore needs either a temporary value or a query shape that preserves the original values explicitly.

Why the Naive Update Fails

Suppose the table has first_name and last_name and you try:

1UPDATE employees
2SET first_name = last_name,
3    last_name = first_name
4WHERE id = 1;

This looks like a swap, but it is not reliable as a swap pattern in MySQL. Once first_name is updated, the second assignment can end up seeing the changed value rather than the original one. The result is often both columns ending up the same.

That is why swap logic needs to preserve the old values somewhere.

Safe Pattern: Temporary Table Snapshot

A clear and reliable approach is to capture the old values, then update from that snapshot.

1CREATE TEMPORARY TABLE employee_swap AS
2SELECT id, first_name AS old_first_name, last_name AS old_last_name
3FROM employees
4WHERE id = 1;
5
6UPDATE employees e
7JOIN employee_swap s ON e.id = s.id
8SET e.first_name = s.old_last_name,
9    e.last_name = s.old_first_name;
10
11DROP TEMPORARY TABLE employee_swap;

This is verbose, but it is easy to reason about because the update reads from stable copied values rather than values being changed in place.

Safe Pattern for Many Rows

The same idea works for more than one row. For example, to swap two columns for every employee in one department:

1CREATE TEMPORARY TABLE employee_swap AS
2SELECT id, first_name AS old_first_name, last_name AS old_last_name
3FROM employees
4WHERE department_id = 10;
5
6UPDATE employees e
7JOIN employee_swap s ON e.id = s.id
8SET e.first_name = s.old_last_name,
9    e.last_name = s.old_first_name;

This keeps the update scoped and avoids accidental modification of unrelated rows.

Numeric Columns May Use Arithmetic Tricks, but Be Careful

For numeric columns only, people sometimes use arithmetic swaps such as:

1UPDATE scores
2SET a = a + b,
3    b = a - b,
4    a = a - b
5WHERE id = 1;

This can work mathematically, but it is usually a poor default because:

• it only fits numeric data
• it is easy to misunderstand later
• large values can introduce overflow risk

For production SQL, clarity is usually worth more than cleverness.

Use Transactions for Safety

If the swap matters, run it inside a transaction so you can verify the result before committing.

1START TRANSACTION;
2
3CREATE TEMPORARY TABLE employee_swap AS
4SELECT id, first_name AS old_first_name, last_name AS old_last_name
5FROM employees
6WHERE id = 1;
7
8UPDATE employees e
9JOIN employee_swap s ON e.id = s.id
10SET e.first_name = s.old_last_name,
11    e.last_name = s.old_first_name;
12
13COMMIT;

During development or one-off admin work, replacing COMMIT with ROLLBACK for a dry run is often a good habit.

Test the Scope First

Before running the UPDATE, inspect the target rows with the same filter used by the swap logic.

SELECT id, first_name, last_name
FROM employees
WHERE id = 1;

This sounds basic, but many destructive SQL mistakes come from getting the WHERE clause wrong, not from the swap logic itself.

Know When an Application-Layer Fix Is Better

If the swap is part of one-time cleanup, SQL is fine. If it is application behavior that happens regularly, it may be easier to express the intent in application code and then persist the corrected values. The database is good at set-based updates, but not every recurring business rule is best encoded as clever SQL.

Common Pitfalls

• Using SET col1 = col2, col2 = col1 and assuming MySQL will preserve the original values automatically.
• Running a swap without checking the affected rows first.
• Using arithmetic swaps for nonnumeric or potentially overflowing values.
• Forgetting to wrap an important bulk update in a transaction.
• Choosing a short but opaque trick instead of a slightly longer query that is easier to verify.

Summary

• A naive two-assignment UPDATE is not a safe general swap pattern in MySQL.
• The reliable approach is to preserve the original values explicitly before updating.
• Temporary-table or snapshot-based swaps are clear and generic.
• Arithmetic tricks are limited and usually less maintainable.
• Use transactions and verify the target rows before running bulk swaps.