Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL syntax for inserting a new row in middle rows?

Tags:

mysql sintax for insert a new row in middle rows or wherever we want without updating the existing row, but automatically increment the primary key (id)?

' id | value ' 1  | 100 ' 2  | 200 ' 3  | 400 ' 4  | 500 

I want to insert a new row after id 2, with a value = 300. I want the output as below:

' id | value ' 1  | 100 ' 2  | 200 ' 3  | 300  <-- new row with id (automatic increment) ' 4  | 400  <-- id=id+1 ' 5  | 500  <-- id=id+1  

Thanks.

like image 785
wira yudha Avatar asked Jun 13 '11 16:06

wira yudha


People also ask

Can you add a new row in the middle of an existing table?

You can add rows to an existing table in two ways: Use Edit > Add Row to enter a new row one-at-a-time. Use File > Import more rows to bring in rows from a file.

Which command is used to add a new row in MySQL?

Syntax of Insert Command in MySQLINSERT INTO table_name is the command that adds a new row into a table named `table_name` in the MySQL database.

How do you insert a row in the middle of a table in MySQL?

When inserting a single row into the MySQL table, the syntax is as follows: INSERT INTO table_name(column_1,column_2,column_3) VALUES (value_1,value_2,value_3); In the INSERT INTO query, you should specify the following information: table_name : A MySQL table to which you want to add a new row.


1 Answers

You will have to split it into 2 operations.

START TRANSACTION;  UPDATE table1 SET id = id + 1 WHERE id >= 3 order by id DESC;  INSERT INTO table1 (id, value) VALUES (3, 300);  COMMIT; 

Notice that you need the order by in the update statement, so it will start with the highest ids first.

Another idea would be to declare id as decimal(10,1) and insert value 2.5 as id in between 2 and 3.

like image 105
Johan Avatar answered Oct 17 '22 07:10

Johan