Replace numeric autoincrement ID with another order
This was a pretty strange request. On a table there were autoincrement IDs, pretty usual when working with MySQL.
But the request was then to reorder the content by date and then reflect that order in the ID column.
Prepare table for order number
Add new column to existing table which should hold the sorted content position.
alter table mytable add column orderid int
Generate new ID
Now we sort/order the tables content and store the position in the sorted result in that new column. In the example we order the content by date.
SET @rownum=0; UPDATE mytable AS i, (SELECT @rownum:=@rownum+1 rownum, id FROM mytable order by mytable.date asc) AS r SET i.orderid = r.rownum WHERE i.id = r.id;
Replace the old ID with the new one
Now prepare the ID column for the new ID by shifting the ID beyond the last entry. We need to do that because MySQL does updates row-by-row and therefore would create ID clashes, when overwriting ID with the new orderID.
UPDATE mytable SET id = id+(select max(id) from mytable);
now we can overwrite the ID with
UPDATE mytable SET id = orderid;
now the ID reflects the order by date.
Since we did update the ID successfully, we can delete our helper column
ALTER TABLE mytable DROP COLUMN orderid;
In my opinion this is not really how a database should be used, this looks more as a request from someone using Excel.