Syntax error Swap data between two columns in MySQL?

Swap data between two columns in MySQL?

To swap data between two columns in MySQL, use the concept of variable. Let us first create a table. Here, we will swap Name1 with Name2 −

mysql> create table DemoTable
-> (
-> Name1 varchar(100),
-> Name2 varchar(100)
-> );
Query OK, 0 rows affected (0.58 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('John Smith','Chris Brown');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable values('David Miller','Jone Doe');
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+--------------+-------------+
| Name1        | Name2       |
+--------------+-------------+
| John Smith   | Chris Brown |
| David Miller | Jone Doe    |  
+--------------+-------------+
2 rows in set (0.00 sec)

Following is the query to swap data between two columns in MySQL −

mysql> update DemoTable
-> SET Name1=(@tmpName:=Name1), Name1 = Name2, Name2 = @tmpName;
Query OK, 2 rows affected (0.13 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Let us check table records once again −

mysql> select *from DemoTable;

Output

This will produce the following output −

+-------------+--------------+
| Name1       | Name2        |
+-------------+--------------+
| Chris Brown | John Smith   |
| Jone Doe    | David Miller |
+-------------+--------------+
2 rows in set (0.00 sec)
Updated on: 2020-06-30T15:06:56+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements