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
Final solution many thanks to Juan Carlos Oropeza for the solution and being patient with me.
UPDATE `my_table` t1
INNER JOIN (SELECT t.*,
@rownum := @rownum + 1 AS `rank`
FROM `my_table` t,
(SELECT @rownum := 0) r
ORDER BY time
) t2
ON t1.`id` = t2.`id`
SET t1.`transaction` = t2.`rank` where t1.`transaction` is null
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
Schema
CREATE TABLE Table1
(`ID` int, `value` int)
;
INSERT INTO Table1
(`ID`, `value`)
VALUES
(10, NULL),
(20, NULL),
(30, NULL)
;
OUTPUT
| ID | value |
|----|-------|
| 10 | 1 |
| 20 | 2 |
| 30 | 3 |
EDIT:
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;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments