Web Development Blog

24K Design Studio :: Blog :: "Order By" can be used in "Update" in MySQL
"Order By" can be used in "Update" in MySQL
19:27, 29 Nov, 2012
by David

About this idea, you won't usually think about it until you really need it. Here is an example where you need it:
Assume you have a `table` with primary key `id` and you have `id` value 1 to 10000 stored in that table. When you are going up adds every `id` by 1, you may write

update `table` set `id`=`id`+1

and this SQL will usually results in SQL error. The reason is simple: `id` is the primay key, and while updating `id`=1 to `id`=2, the duplication happened.

And the solusion is

update `table` set `id`=`id`+1 order by `id` desc
which means the order of updating can be defined to avoid relative unique-duplication problem.

Tags: MySQL
Your name:
Only visible to the author.

Tell us you are not a robot. What is captcha1captchaOcaptcha2

View the latest 5 entries.