rome 웃 rome 웃 - 2 years ago 95
SQL Question

How to make execution update reverse query mysql by id?

I have 5 rows with ID: 1,2,3,4,5.

Then I made a query to try change those ID to: 2,3,4,5,6

Update table set `id` = `id` + 1

Then i get:

Duplicate entry '2' for key 'PRIMARY'.

I understand this query be executed by ID 1 first and the cause is the PRIMARY 2 was existed.

So my question is how to make a query has been executed by ID from 5 to 1.

Answer Source

Created a table num_sample with numbers from 1 to 100. Defined a primary key on the column num. And wrote this update

update num_sample set num = num + 1 order by num desc limit 100;

Worked absolutely fine in MySQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download