Is it possible to insert data into the mid section of a table using the INSERT command?

Question

Example:

INDEX | SOME_DATA

1 | some_data1

3 | some_data3

4 | some_data4

5 | some_data5

I want to do an INSERT to add ---> 2 | some_data2. Is there an SQL command or some way of doing this?

[edit]
OK. Maybe if i explain every thing what i want to accomplish you'll see why i'm trying to do what i want to do. I creating a Web admin page in php that simply displays data from a database table.now these displayed rows are "draggable". example,you can drag row #2 to say, row #4. now on the "mouseup" event, i want to save the new order in the database.

1| data1 -------------------------> 1| data1
2| data2 -------After Drag-----> 3| data3
3| data3 -------------------------> 4| data4
4| data4 -------------------------> 2| data2
See my problem? i cant merely just update the value in the INDEX column for the one value that was dragged. I'll have to update all the values that were dragged over. 3 has to become a 2, 4 a 3 and 2 has to become the new 4. Thats too many sql updates (expecially if the table is larger). So i want to remove row #2 and insert it in its correct location.

Answer 1

Just insert the data; tables are not fundamentally ordered. The only ordering of table results occurs when you define it yourself in your select statement.

Edit: if what you want is to have a separate ordering, you would be well served by having a separate "order" column. I'd recommend making it of type float, so you can insert entries anywhere between other entries without requiring any updating. For example, if you have entry "A" with "order" column value "1", entry "B" with "order" column value "2", and you want to insert entry "C" so it shows up between entry "A" and entry "B", just insert it with the "order" column value set to 1.5 (1 + 2 / 2.0). That way, you can perform your select with an "order by" on your "order" column, and things should turn out the way you want. Floats are a reasonably good solution to this issue, since there's enough space to support a lot of "in-between" adds.

Answer 2

You should just be able to add it like regular:

INSERT INTO some_table (index, some_data) VALUES (2, 'some_data2')

After inserting, if you actually want the rows to be ordered by index, run this:

ALTER TABLE some_table ORDER BY index;

In general the order is really unimportant though. You should be using indexes for columns you do finds on, and ORDER BY when getting result sets. You should not rely on the insert order of rows in the table for either optimizing or sorting.

Answer 3

Yes, you can specify the primary key of the row when you insert.

insert into yourtablename (index, some_data) values (2, "some_data2");

It's a bad idea however to worry about having contiguous primary keys. Their job should be to uniquely identify the row and nothing more.

Answer 4

Okay, I'm answering again because your update shows you are asking a completely different question than everyone thought you were.

The method that you described -- update the index of the moved row and every one in the list after it -- is exactly the right method here.

Why do you say that "that's too many sql updates"? As long as you have a MySQL index applied to the index column, those updates will be lightening fast, even for very large lists (they will be on the order of milliseconds).

I think you are either pre-optimizing, or you don't have an index defined. To add an index (if you don't already have one), run this command:

CREATE INDEX row_index_index ON `some_table` (`index`);

You also might want to reconsider renaming your "index" column to something like "position" to avoid confusion about nomenclature with an actual MySQL index.