Peter Bushnell Peter Bushnell - 4 months ago 24
PHP Question

MySQL Update null column values incrementally in time order

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

Answer

SQL Fiddle Demo

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

DEMO

alter table `wp_user_txs` add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;
Comments