Can the sorting from an ORDER BY statement be saved explicitly with only one UPDATE statement?

by Daniel Rikowski   Last Updated October 29, 2018 09:06 AM

I have this table:

SELECT * FROM items

id | item      | position
---|-----------|----------
 1 | USB cable |        0
 2 | SD card   |        4
 3 | Mouse     |        2
 4 | Keyboard  |        0
 5 | Monitor   |        3

Sorting this table gives this result:

SELECT * FROM items ORDER BY position

id | item      | position
---|-----------|----------
 4 | Keyboard  |        0
 1 | USB cable |        0
 3 | Mouse     |        2
 5 | Monitor   |        3
 2 | SD card   |        4

Now I want to update the table and save the order in the position column:

SELECT * FROM items

id | item      | position
---|-----------|----------
 4 | Keyboard  |        1
 1 | USB cable |        2
 3 | Mouse     |        3
 5 | Monitor   |        4
 2 | SD card   |        5

Can this be done with a single query or do I have to manually loop over all rows and do a manual update?

In case the order is not fully defined (e.g. for USB cable and Keyboard above) I just arbitrarily decided the order.

Tags : update order-by


Answers 2


UPDATE
   T1
SET
   position = T2.rn
FROM
   myTable T1
   JOIN
   (
   SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY position) AS rn
   FROM
      myTable
   ) T2 ON T1.id = T2.id

Note: the order of "Keyboard" and "USB cable" is arbitrary. They both have position = 0

To tie-break positions based on item, add a secondary sort

      ROW_NUMBER() OVER (ORDER BY position, item) AS rn

If you have duplicate position,item pairs this will be arbitrary too...

gbn
gbn
August 28, 2011 17:45 PM

Here is how to achieve this in MySQL:

set @x = 0;
update items A
inner join
(
    select @x:[email protected]+1 newposition,id
    from items
    order by position
) B using (id)
set A.position=B.newposition;

I used your sample data from the question, loaded it into MySQL 5.5.15 on my laptop, and ran those two lines. Here is the result:

mysql> use test
Database changed
mysql> drop table if exists items;
Query OK, 0 rows affected (0.01 sec)

mysql> create table items
    -> (
    ->     id int not null auto_increment,
    ->     item varchar(20),
    ->     position int,
    ->     primary key(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into items (item,position) values
    -> ('USB cable',0),('SD Card',4),
    -> ('Mouse',2),('Keyboard',0),('Monitor',3);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from items;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        0 |
|  2 | SD Card   |        4 |
|  3 | Mouse     |        2 |
|  4 | Keyboard  |        0 |
|  5 | Monitor   |        3 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> select * from items order by position;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        0 |
|  4 | Keyboard  |        0 |
|  3 | Mouse     |        2 |
|  5 | Monitor   |        3 |
|  2 | SD Card   |        4 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> set @x = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update items A
    -> inner join
    -> (
    ->     select @x:[email protected]+1 newposition,id
    ->     from items
    ->     order by position
    -> ) B using (id)
    -> set A.position=B.newposition;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from items;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        1 |
|  2 | SD Card   |        5 |
|  3 | Mouse     |        3 |
|  4 | Keyboard  |        2 |
|  5 | Monitor   |        4 |
+----+-----------+----------+
5 rows in set (0.00 sec)

mysql> select * from items order by position;
+----+-----------+----------+
| id | item      | position |
+----+-----------+----------+
|  1 | USB cable |        1 |
|  4 | Keyboard  |        2 |
|  3 | Mouse     |        3 |
|  5 | Monitor   |        4 |
|  2 | SD Card   |        5 |
+----+-----------+----------+
5 rows in set (0.00 sec)
RolandoMySQLDBA
RolandoMySQLDBA
August 28, 2011 23:04 PM

Related Questions


Updates with many rows and lock escalation

Updated November 07, 2018 16:06 PM

Scrambling PII data in CLOB columns

Updated September 11, 2018 07:06 AM

SQL: Custom Sort Order

Updated March 28, 2017 11:06 AM


SQL SELECT Parent-Child with SORT

Updated November 26, 2018 21:06 PM