Swap Column Values in SQL Server

There are multiple methods to swap column values in SQL Server. Here are three methods:

Method 1: Using a temporary variable

DECLARE @temp AS datatype
UPDATE table_name
SET @temp = column1, column1 = column2, column2 = @temp

In this method, we declare a temporary variable and use it to swap the values of the two columns.

Example:

Let's say we have a table named "employees" with columns "first_name" and "last_name". We want to swap the values of these two columns.

SELECT * FROM employees

first_name | last_name
-----------|----------
John       | Doe
Jane       | Smith

We can use the following query to swap the values:

DECLARE @temp AS varchar(50)
UPDATE employees
SET @temp = first_name, first_name = last_name, last_name = @temp

After running this query, we can see that the values of the two columns have been swapped:

SELECT * FROM employees

first_name | last_name
-----------|----------
Doe        | John
Smith      | Jane

Method 2: Using a CASE statement

UPDATE table_name
SET column1 = 
    CASE 
        WHEN column1 = value1 THEN value2 
        ELSE value1 
    END,
    column2 = 
    CASE 
        WHEN column2 = value2 THEN value1 
        ELSE value2 
    END

In this method, we use a CASE statement to check the current value of each column and swap the values accordingly.

Example:

Let's say we have a table named "students" with columns "first_name" and "last_name". We want to swap the values of these two columns where the first name is "John" and the last name is "Doe".

SELECT * FROM students

first_name | last_name
-----------|----------
John       | Doe
Jane       | Smith

We can use the following query to swap the values:

UPDATE students
SET first_name = 
    CASE 
        WHEN first_name = 'John' AND last_name = 'Doe' THEN last_name 
        ELSE first_name 
    END,
    last_name = 
    CASE 
        WHEN first_name = 'John' AND last_name = 'Doe' THEN first_name 
        ELSE last_name 
    END

After running this query, we can see that the values of the two columns have been swapped:

SELECT * FROM students

first_name | last_name
-----------|----------
Doe        | John
Jane       | Smith

Method 3: Using a temporary table

SELECT column2 AS temp_column, column1, column2
INTO #temp_table
FROM table_name

UPDATE table_name
SET column1 = temp_column
FROM #temp_table
WHERE table_name.column2 = #temp_table.column2

DROP TABLE #temp_table

In this method, we create a temporary table with the values of the two columns swapped. We then use this temporary table to update the original table.

Example:

Let's say we have a table named "orders" with columns "order_id" and "customer_id". We want to swap the values of these two columns where the order ID is 100.

SELECT * FROM orders

order_id | customer_id
---------|------------
100      | 1
200      | 2

We can use the following query to swap the values:

SELECT customer_id AS temp_customer_id, order_id
INTO #temp_table
FROM orders
WHERE order_id = 100

UPDATE orders
SET customer_id = temp_customer_id
FROM #temp_table
WHERE orders.order_id = #temp_table.order_id

DROP TABLE #temp_table

After running this query, we can see that the values of the two columns have been swapped:

SELECT * FROM orders

order_id | customer_id
---------|------------
100      | 1
200      | 2