MySQL - UPDATE one column's values using one query

Question

I have this db table:

Items
-------
id  // values: 1,2,3,4,5 ... 
name // values: Item1,Item2,Item3,Item4,Item5 ...
order // values: 1, 5, 2, 3, 4 ...

The items are images. The image order is set in an array when dragging the images around, but in order to update the order, I use one UPDATE inside a loop. I was wondering if I could do the UPDATE without using a loop.

I was wondering if I can update the item's order at once, using only one UPDATE query.

Depends what you want the value of order to be. If they're just supposed to be the same as the id you can do UPDATE Items SET order = id but I doubt thats what you want. Please clarify. — Moob
– Moob, Commented Mar 14, 2014 at 15:37
The items are images. The image order is set in an array when dragging the images around, but in order to update the order, I use one UPDATE inside a loop. I was wondering if I could do the UPDATE without using a loop. — ltdev
– ltdev, Commented Mar 14, 2014 at 15:40
The order will be updated to the new order set. Please review the post, I did an edit — ltdev
– ltdev, Commented Mar 14, 2014 at 15:44

Halcyon · Accepted Answer · 2014-03-14 15:36:27Z

1

You can use INSERT (..) ON DUPLICATE KEY UPDATE (..) syntax.

INSERT INTO `table` (`id`, `order`)
VALUES (1, 1), (2, 5), (3, 2), (4, 3), (5, 4)
ON DUPLICATE KEY UPDATE `order` = VALUES(`order`)

answered Mar 14, 2014 at 15:36

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL - UPDATE one column's values using one query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related