[adsense id=”0514458240″ width=”468″ height=”60″]
MySQL offers some powerful options with user defined variables. In this post we are going to show how to renumber your unique identifier in a table. Please be aware that if these unique identified values are used elsewhere you have to also perform updates elsewhere which can be complex and is not supported by the operation being shown here. Here we are simply showing the power of user defined variables.
If this is something that you need please do not hesitate in contacting us on data migration or data manipulation operations. Consider a table emp with empid as the primary unique identifier and we need to reset these values back and incrementing from 1.
mysql> select * from emp; +-------+-------+--------+ | empid | name | deptid | +-------+-------+--------+ | 49 | Tom | 1 | | 50 | Dick | 2 | | 51 | Harry | NULL | +-------+-------+--------+ 3 rows in set (0.00 sec)
We first need to set the user defined variable to @empid = 0 and then update while at the same time performing an update to the value of the user defined variable @empid as follows:
mysql> set @empid = 0; Query OK, 0 rows affected (0.00 sec) mysql> update emp set empid = @empid:=@empid+1; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select * from emp; +-------+-------+--------+ | empid | name | deptid | +-------+-------+--------+ | 1 | Tom | 1 | | 2 | Dick | 2 | | 3 | Harry | NULL | +-------+-------+--------+ 3 rows in set (0.00 sec)
[googleplus]