There is a table with hundreds of entries and six columns. One of those columns, the 'transaction' column, has been filling up with null values, we now need that column to have unique values, existing entries are unique.
There is a 'time' datetime column, ideally the 'transaction' column would be updated incrementally with integers starting from 1 in 'time' order.
This seems simple but after several times dropping and restoring a live database it now seems less simple and a little help would be greatly appreciated.
SQL Fiddle - Sample of live database
UPDATE Table1 t1 INNER JOIN (SELECT t.*, @rownum := @rownum + 1 AS `rank` FROM Table1 t, (SELECT @rownum := 0) r // ORDER BY time use your datetime field ) t2 ON t1.`ID` = t2.`ID` // You need a PK field SET t1.`value` = t2.`rank` // Update your sequence field
CREATE TABLE Table1 (`ID` int, `value` int) ; INSERT INTO Table1 (`ID`, `value`) VALUES (10, NULL), (20, NULL), (30, NULL) ;
| ID | value | |----|-------| | 10 | 1 | | 20 | 2 | | 30 | 3 |
or just create a new ID field to be PK
alter table `wp_user_txs` add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;