1

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".

FYI, 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.

humble_coder
  • 2,777
  • 7
  • 34
  • 46

2 Answers2

2

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...

Community
  • 1
  • 1
alan
  • 21
  • 3
0

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.

Hong Ning
  • 1,003
  • 7
  • 7
  • Yeah, I was trying to avoid the data copy etc. I might just end up doing this. Thanks. – humble_coder May 26 '11 at 17:50
  • Another way could be using Row_Number, like this: SELECT ROW_NUMBER() OVER (ORDER BY last_name) AS RowId, * FROM your_table. Then update RowId to the display_order. – Hong Ning May 26 '11 at 19:26
  • Ok, and say I wanted to simply update the row number sequentially. How would I do that in that scenario? – humble_coder May 26 '11 at 19:47