[adsense id=”0514458240″ width=”468″ height=”60″]
I have seen this requested many times and MySQL offers sessions variables which allows values to be temporarily stored. This is extremely handy when a table consists of columns where the values in each of the fields need to be changed. For example, let us imagine we have a table with fields f1 and f2. The value stored in f1 should have been stored in f2 and vice versa. How can we resolve this?
If we go down the road of a traditional UPDATE statement we would see the following:
mysql> select * from updatefields; +------+------+ | f1 | f2 | +------+------+ | 0 | 1 | | 2 | 3 | | 3 | 4 | +------+------+ 3 rows in set (0.00 sec) mysql> update updatefields set f2 = f1, f1 = f2; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from updatefields; +------+------+ | f1 | f2 | +------+------+ | 0 | 0 | | 2 | 2 | | 3 | 3 | +------+------+ 3 rows in set (0.01 sec)
The problem in this case is that the value f1 was updated into field f2 and f2’s new value updates f1. What we want in this case is to temporarily store the f1 value so that it can then be used later on to update the f2 field.
mysql> select * from updatefields; +------+------+ | f1 | f2 | +------+------+ | 0 | 1 | | 2 | 3 | | 3 | 4 | +------+------+ 3 rows in set (0.00 sec) mysql> update updatefields set f1=@f1:=f1, f1=@f2:=f2, f2=@f1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from updatefields; +------+------+ | f1 | f2 | +------+------+ | 1 | 0 | | 3 | 2 | | 4 | 3 | +------+------+ 3 rows in set (0.00 sec)
In this case here what we have done is to assign to session variable @f1 the value of f1 which updates the field f1. This is necessary otherwise the operation would fail. Once we have store the field value f1 in @f1 we can then perform the update f1 with the value of f2 and then f2 with the temporarily stored value in @f1.
[googleplus]