Database Column rows exchange Query in SQL

Question

I have to exchange the column data from one set of rows to another set of rows Can anyone explain?

Jayraj Chhaya · 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.

Jaish Mathews · Answer

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 .

Kiran Kumar · Answer

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

Kiran Kumar · Answer

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

Database Column rows exchange Query in SQL

Answers (4)