MySQL syntax for inserting a new row in middle rows?

user_796308 0
Viewed 22281

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.

insert mysql
4 Answers

PostgreSQL insert new row after current row (python)

eg current row has id 6 and I need to insert new row with id 7

lastIdString = "select max(id) from sheet2_Spare_SFPs;"
lastIdQuery = cursor.execute(lastIdString);
lastIdResult = cursor.fetchone()
lastId = lastIdResult[0]
futureOrder = currentrow_id + 1
newUpdateMax = 2 + currentrow_id

# first step move all rows that bigger than target new id eg : 
# new value should be in 6 any thing > 6 moved starting from max id
# this will make the id after the current row empty

temporarySquence1 = "create temporary sequence IF NOT EXISTS seq_upd;"
temporarySquence01 = "select setval('seq_upd', (select max(id) from sheet2_Spare_SFPs) + %s);"%newUpdateMax
temporarySquence001 = "update sheet2_Spare_SFPs set id=nextval('seq_upd') where id>%s;"%currentrow_id
cursor.execute(temporarySquence1)
cursor.execute(temporarySquence01)
cursor.execute(temporarySquence001)

# insert new value after current row eg 7 set the id as futrue id which is current + 1

insertTheNewRow = "INSERT INTO sheet2_Spare_SFPs (id, PID) VALUES (%s, 'hi');"%futureOrder
cursor.execute(insertTheNewRow)
# last step back the group to the new sequence any item > futre id
temporarySquence2 = "create temporary sequence IF NOT EXISTS seq_two;"
temporarySquence02 = "select setval('seq_two', %s, false);"%newUpdateMax
temporarySquence002 = "update sheet2_Spare_SFPs set id=nextval('seq_two') where id>%s;"%futureOrder

cursor.execute(temporarySquence2)
cursor.execute(temporarySquence02)
cursor.execute(temporarySquence002)
user_116866740
Related