Swap Values of 2 Columns in SQL Server

In SQL Server, you can swap the values of two columns using various methods. This is useful when you want to exchange the values of two columns in a table.

Method 1: Using a Temporary Variable

You can use a temporary variable to hold the value of one column, then update the column with the value of the other column, and finally update the other column with the value from the temporary variable.

DECLARE @Temp varchar(50); UPDATE YourTable SET @Temp = Column1, Column1 = Column2, Column2 = @Temp; 

This method is simple, but it has some limitations. It only works for a single row, and it's not very efficient for large tables.

Method 2: Using a Self-Join

You can use a self-join to update the table, swapping the values of the two columns.

UPDATE t1 SET t1.Column1 = t2.Column2, t1.Column2 = t2.Column1 FROM YourTable t1 INNER JOIN YourTable t2 ON t1.PrimaryKey = t2.PrimaryKey; 

This method is more efficient than the first method, but it still has some limitations. It requires a primary key or a unique identifier to join the table with itself.

Method 3: Using a Single UPDATE Statement

You can use a single UPDATE statement with a clever trick to swap the values of the two columns.

UPDATE YourTable SET Column1 = Column2 + (Column1 - Column2); 

This method is the most efficient and elegant way to swap the values of two columns. It works by adding the difference between the two columns to the first column, effectively swapping their values.

Example: Swapping Values of Two Columns

Let's say we have a table called `Employees` with two columns: `FirstName` and `LastName`. We want to swap the values of these two columns.

Using the third method, we can swap the values of the `FirstName` and `LastName` columns with a single UPDATE statement:

UPDATE Employees SET FirstName = LastName + (FirstName - LastName); 

The resulting table would be:

Important Notes

When swapping values of two columns, keep in mind:

• Make sure to backup your data before making any changes.
• Test the UPDATE statement in a development environment before running it in production.
• Be careful when swapping values of columns with different data types or lengths.