Update order column from a list of IDS

Question

I have a list of N ids (for example: 803, 518, 361) on a MySQL Server 5.7.24

Current values are

+-----+-------+
| id  | order |
+-----+-------+
| 361 |     1 |
| 518 |     2 |
| 803 |     3 |
+-----+-------+

I want to update order column from a list of ids, getting order from id position.

This query returns the correct rownum for each id:

SELECT `id`, (@rownumber := @rownumber + 1) `rownum`
FROM `comments`
CROSS JOIN (SELECT @rownumber := 0) `cross`
WHERE `id` IN (803, 518, 361)
ORDER BY FIELD (`id`, 803, 518, 361) ASC;

+-----+--------+
| id  | rownum |
+-----+--------+
| 803 |      1 |
| 518 |      2 |
| 361 |      3 |
+-----+--------+

But on update, this positions are not set:

UPDATE `comments` `target`
JOIN (
    SELECT `id`, (@rownumber := @rownumber + 1) `rownum`
    FROM `comments`
    CROSS JOIN (SELECT @rownumber := 0) `cross`
    WHERE `id` IN (803, 518, 361)
    ORDER BY FIELD (`id`, 803, 518, 361) ASC
) `source` ON `target`.`id` = `source`.`id`
SET `target`.`order` = `source`.`rownum`;

Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

The order value was not updated:

SELECT `id`, `order` FROM `comments`
WHERE `id` IN (361, 518, 803)
ORDER BY `order` ASC;

+-----+-------+
| id  | order |
+-----+-------+
| 361 |     1 |
| 518 |     2 |
| 803 |     3 |
+-----+-------+
3 rows in set (0.01 sec)

Where is the problem on the queries?

Thanks!

UPDATE 1:

Also, not working

UPDATE `comments` `target`, (
    SELECT `id`, (@rownumber := @rownumber + 1) `rownum`
    FROM `comments`
    CROSS JOIN (SELECT @rownumber := 0) `cross`
    WHERE `id` IN (803, 518, 361)
    ORDER BY FIELD (`id`, 803, 518, 361) ASC
) `source`
SET `target`.`order` = `source`.`rownum`
WHERE `target`.`id` = `source`.`id`;

@P.Salmon oh, it's strange... I have a script with fixed ids on SQL to execute in a row to test, and is not working. — Lito, Jan 11 '19 at 14:02
`FIELD (id, 803, 518, 361)` in the first query vs `FIELD (803, 518, 361)` in the second one -- is that an error in the copy you posted or also different in the code you actually use? — sticky bit, Jan 11 '19 at 14:03
@stickybit was an copy&paste error. In my code I'm using `FIELD (id, 803, 518, 361)`. — Lito, Jan 11 '19 at 15:17

score 1 · Answer 1 · answered Jan 13 '19 at 18:03

1

The following query should do the trick :

SET @rownumber = 0;
UPDATE comments
SET `order` = @rownumber:= @rownumber + 1
WHERE id IN (803, 518, 361)
ORDER BY id DESC;

See this db fiddle

answered Jan 13 '19 at 18:03

GMB

216,147
25
84
135

I can't do it because is from a PHP script and I only can execute one query at once. Multiples queries are not allowed. – Lito Jan 14 '19 at 18:03
[mysqli::multi_query](http://php.net/manual/en/mysqli.multi-query.php) is your friend @Lito – GMB Jan 14 '19 at 18:06
I'm using PDO ;) – Lito Jan 14 '19 at 18:07
1

@Lito : ok then you can simply run the two statements, one after the other – GMB Jan 14 '19 at 18:15
1

Example here : https://stackoverflow.com/questions/24958367/php-pdo-using-mysql-variables – GMB Jan 14 '19 at 18:17

score 1 · Accepted Answer · answered Jan 14 '19 at 20:16

Since id is the primary key, you could simply do:

UPDATE `comments`
       SET `order` = field(`id`,
                           803,
                           518,
                           316)
       WHERE `id` IN (803,
                      518,
                      361);

field() already produces the 1, 2 or 3.

score 0 · Answer 3 · answered Jan 14 '19 at 18:23

As I'm working with queries on PHP, thanks to @GMB with idea about run two different statements:

$db->exec('SET @rownumber = 0;');

$db->exec('
    UPDATE `comments`
    SET `order` = @rownumber := @rownumber + 1
    WHERE `id` IN (803, 518, 361)
    ORDER BY FIELD (`id`, 803, 518, 361) ASC;
');

Update order column from a list of IDS

3 Answers3