CODEWITHSUNDEEP
mysql
Lito
Lito

Reputation: 1332

Update order column from a list of IDS

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`;

Upvotes: 1

Views: 1105

Answers (3)

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 1

Lito
Lito

Reputation: 1332

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;
');

Upvotes: 0

GMB
GMB

Reputation: 222482

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

Upvotes: 1

Related Questions