Swap the Values of Two Columns in SQL Server

Estimated study time: 2 minutes. A quick, practical fix you'll use more often than you'd expect.

Sometimes you need to exchange the values stored in two columns — for example, swapping first_name and last_name after a data entry mistake. SQL Server gives you a few clean ways to do this.

Method 1: Single UPDATE Statement

SQL Server evaluates the SET clause using the original row values, so you can swap columns directly in one statement — no temporary variable needed:

UPDATE employees
SET first_name = last_name,
    last_name = first_name;

Method 2: Using a Temporary Variable (Row by Row)

DECLARE @temp VARCHAR(100);

SELECT @temp = first_name FROM employees WHERE employee_id = 101;

UPDATE employees
SET first_name = last_name
WHERE employee_id = 101;

UPDATE employees
SET last_name = @temp
WHERE employee_id = 101;

Method 3: Using CASE for Conditional Swaps

UPDATE products
SET price = CASE WHEN category = 'Sale' THEN cost ELSE price END,
    cost = CASE WHEN category = 'Sale' THEN price ELSE cost END;

When You'd Use This

• Fixing data entry errors where two fields were reversed
• Reassigning values during a data migration
• Quick fixes during QA or testing on a staging database