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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With