CODEWITHSUNDEEP
mysqlsql
Lee Price
Lee Price

Reputation: 5212

Reorder rows in a MySQL table

I have a table:

+--------+-------------------+-----------+
| ID     | Name              |  Order    |
+--------+-------------------+-----------+
| 1      | John              | 1         |
| 2      | Mike              | 3         |
| 3      | Daniel            | 4         |
| 4      | Lisa              | 2         |
| 5      | Joe               | 5         |
+--------+-------------------+-----------+

The order can be changed by admin hence the order column. On the admin side I have a form with a select box Insert After: to entries to the database. What query should I use to order+1 after the inserted column.

I want to do this in a such way that keeps server load to a minimum because this table has 1200 rows at present. Is this the correct way to save an order of the table or is there a better way?

Any help appreciated

EDIT:

Here's what I want to do, thanks to itsmatt:

want to reorder row number 1 to be after row 1100, you plan to leave 2-1100 the same and then modify 1 to be 1101 and increment 1101-1200

Upvotes: 3

Views: 17056

Answers (3)

StAlphonzo
StAlphonzo

Reputation: 766

you can

insert into tablename (name, `order`) 
values( 'name', select `order`+1 from tablename where name='name')

you can also you id=id_val in your inner select.

Hopefully this is what you're after, the question isn't altogether clear.

Upvotes: -2

BG100
BG100

Reputation: 4531

You need to do this in two steps:

UPDATE MyTable 
   SET `Order` = `Order` + 1 
 WHERE `Order` > (SELECT `Order` 
                    FROM MyTable 
                   WHERE ID = <insert-after-id>);

...which will shift the order number of every row further down the list than the person you're inserting after.

Then:

INSERT INTO MyTable (Name, `Order`)
VALUES (Name, (SELECT `Order` + 1 FROM MyTable WHERE ID = <insert-after-id>));

To insert the new row (assuming ID is auto increment), with an order number of one more than the person you're inserting after.

Upvotes: 10

Pete Wilson
Pete Wilson

Reputation: 8694

Just add the new row in any normal way and let a later SELECT use ORDER BY to sort. 1200 rows is infinitesimally small by MySQL standards. You really don't have to (and don't want to) keep the physical table sorted. Instead, use keys and indexes to access the table in a way that will give you what you want.

Upvotes: 1

Related Questions