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.

Before:

After:

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.

Cleaning up

Since we did update the ID successfully, we can delete our helper column orderid

ALTER TABLE mytable DROP COLUMN orderid;

My Advice

In my opinion this is not really how a database should be used, this looks more as a request from someone using Excel.