Database Column rows exchange Query in SQL

Kiran Kumar

1y717SQL Server

I have to exchange the column data from one set of rows to another set of rows

Can anyone explain?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayraj Chhaya1y ago
Accepted answer
Hi Kiran Kumar,

To exchange column data between two sets of rows in SQL, you can utilize a temporary variable or a common table expression (CTE) to facilitate the swap. Below is a simple example using a temporary variable approach.

Assuming you have a table named Employees with columns ID, Name, and Salary, and you want to exchange the Salary values of two specific employees (e.g., IDs 1 and 2):
```
-- Step 1: Store the Salary of the first employee in a temporary variable
DECLARE @TempSalary DECIMAL(10, 2);

SELECT @TempSalary = Salary FROM Employees WHERE ID = 1;

-- Step 2: Update the Salary of the first employee with the second employee's Salary
UPDATE Employees
SET Salary = (SELECT Salary FROM Employees WHERE ID = 2)
WHERE ID = 1;

-- Step 3: Update the Salary of the second employee with the temporary variable
UPDATE Employees
SET Salary = @TempSalary
WHERE ID = 2;
```
This method ensures that the Salary values are exchanged without losing any data. Always ensure to back up your data before performing such operations to prevent any accidental data loss.
+3
Kiran Kumar1y ago
Hi Mathews

Thanks for simplified it into single query but again it will update only one record by Id right, not the group of rows

Here is my table

Id Department

1 IT

2 IT

3 Admin

4 Admin

5 IT

All IT cells should be changed to Admin

And

All Admin cells should be changed to IT

Condition

There should not be any change in the Id column as its primary unique

This scenario where users have selected or entered different department by mistake and that need to be corrected

Please provide the query
+1
Jaish Mathews1y ago
You ,may try using CASE as below, I didn't execute this though.

UPDATE Employees
SET Salary = CASE
WHEN ID = 1 THEN (SELECT Salary FROM Employees WHERE ID = 2)
WHEN ID = 2 THEN (SELECT Salary FROM Employees WHERE ID = 1)
END
WHERE ID IN (1, 2);

Notes:
- Ensure you have a condition to uniquely identify the rows (e.g., ID).
- The SELECT statements inside the CASE clause work correctly because the SQL engine evaluates them independently for each row before performing the UPDATE.
+2
Kiran Kumar1y ago
Hi Jay

I accept your solution but is there any way we can get this done by not using temp table

Also, just for an update on the question I can see your code it update only specific column row value that is by id, but I need to exchange a set of rows to be updated example by departments within the same table

Example Data stored in the format of specific employee's designation not entered for specific department I want that to bring from another department's role and that department roles have to be updated by the charging first time department rows

It is kind of exchanges the same set of data bw rows for that particular group of rows
+1

Id	Department
1	IT
2	IT
3	Admin
4	Admin
5	IT