Wednesday, December 5, 2007

An SQL Update query question ?

A friend of mine yesterday asked me an SQL update question the question is like this..
lets say we have a table called t1 with the following structure
mysql> desc t1;
+-------+------------+
| Field | Type |
+-------+------------+
| a | varchar(2) |
| b | varchar(2) |
+-------+------------+
I inserted some sample data into the table after which the table looks like
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| aa | bb |
| aa | bb |
| aa | bb |
| aa | bb |
| aa | bb |
| aa | bb |
+------+------+
Now the question is that using one sql update query you shuffle the data of column a to column b and the table should look like
mysql> select * from t1;
+------+------+
| a | b |
+------+------+
| bb | aa |
| bb | aa |
| bb | aa |
| bb | aa |
| bb | aa |
| bb | aa |
+------+------+

I thought about this overnight but failed to find a solution, this morning he told me that we can do it the following way.

mysql> update t1 as az, t1 as bz set az.a=bz.b, bz.b=bz.a;
Interesting, isint it.

No comments: