Using SQL to batch update columns based on position in resulting sort operation?

My brain decided to stop working this morning. I need a MySQL and SQLite compatible query that will allow me to batch update the "display_order" column of a table based on sorting by another column.

For example, say I have the following fields: display_order, first_name, last_name, date_created. I'd like to say "sort by last_name and update display_order to be sequential".

F开发者_StackOverflowYI, the database is much more complex than just those fields, and things were so poorly designed that complex sorting on the fly takes decades. That said, we need to get the data in order for a given report,transaction, etc ahead of time so that a straight "SELECT * FROM tbl ORDER BY display_order" works.

Best.

Here is what I did when I wanted to update a sequence column with the sort of the table's title. (Normally the sort order is fixed by the true position in a series, but a "holder" set of miscellaneous one-off talks needs to be sorted by the title and has to be updated every time a new talk is added).

UPDATE resource r
JOIN(
SELECT
    resource.resource_key AS rkey,
    @curRow := @curRow + 1 AS row_number
FROM
    resource,
    (SELECT @curRow := 0) rtemp
WHERE
    grouping_key = 'MMMISC'
ORDER BY
    resource.title
) c ON r.resource_key = c.rkey
SET r.group_sequence = c.row_number

Hat tip to this and this for inspiring the answer...

P.S. I do not know if it works in SQLite, but it does in MySQL...

I'm not sure this works for you or not. Create a temp table with an AUTO_INCREMENT column "ID" along with other columns similar to your_table, and insert the temp table with the result of "select * from your_table order by last_name". And then update your_table.display_order column with temp.ID where your_table.last_name = temp.last_name.