In this tutorial, we will learn how to swap column values in SQL.
It is pretty straightforward in many databases like SQL Server and only gets a bit challenging in MySQL.
For our example, we are going to be working with a Coordinates table.
Please find the DDL below -
Create table Coordinates
(x int,
y int);
Insert into Coordinates Values (1,2);
Insert into Coordinates Values (1,4);
Insert into Coordinates Values (4,2);
Insert into Coordinates Values (2,4);
Insert into Coordinates Values (3,3);
Insert into Coordinates Values (3,0);
For SQL Server , a single Update statement is sufficient to achieve this requirement.
Update Coordinates
Set x = y, y = x;
For MySQL,
1st approach - (x = 1, y = 2)
Step 1 - Store sum of two column values in first column
x = x + y
x = 1 + 2 = 3
Step 2 - Subtract 2nd column value(y) from the above (Step 1) stored value in x and assign to 2nd column.
y = x -y
y = 3 - 2 = 1
Step 3 - Subtract 2nd column value from Step 2 above (y) from the value in x from Step 1 and assign to first column.
x = x -y
x = 3 - 1 = 2
Result - (x = 2, y = 1)
UPDATE Coordinates
--SET x = x+y,
-- SET y = x-y,
SET x = x-y;
2nd approach - Use a temporary variable to store the intermediate value
declare @temp int = 0;
declare @cnt int = 0;
update coordinates
set @temp = x, x = y, y = @temp , @cnt = @cnt + 1;
Select @cnt