26

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.

miku
  • 181,842
  • 47
  • 306
  • 310
wira yudha
  • 275
  • 1
  • 3
  • 5
  • 3
    Changing primary key values like this is rarely a good idea. Any reason you need to renumber the field? – Marc B Jun 13 '11 at 17:09
  • I need it. There are some data which I grouped into the group at the table. ID simply serves as a primary key when the search only, not representing the id of the existing data(row). I have the id itself, to represent the data grouped in the table. – wira yudha Jun 15 '11 at 15:24

4 Answers4

45

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.

Johan
  • 74,508
  • 24
  • 191
  • 319
  • @Johan, what happens if two users cause this code to run at the same time? Is there a way of blocking the second user until the first one has finished both operations? Otherwise things can go seriously wrong. – Ash Jun 15 '11 at 08:20
  • @Michas, do I need to change the code above to turn it into a transaction or is it being done automatically? – Ash Jun 21 '11 at 09:53
  • 1
    The code above was edited and now include transaction. However, You have to use InnoDB as table engine. – Michas Jun 21 '11 at 10:24
  • 1
    You don't need to renumber anything if the 'id' you want to INSERT or UPDATE doesn't exist . This doesn't check anything. – roetnig Dec 20 '16 at 12:00
0
insert into my_table (value)
values (select value+100 from my_table order by id desc limit 1)
Senad Meškin
  • 13,597
  • 4
  • 37
  • 55
  • 1
    I don't think that will produce the results OP wants. Something needs to be done to each row after the insertion point. – Ted Hopp Jun 13 '11 at 17:01
0

You can't use the autoincrement feature for the new row. The next autoincrement value will always be after the last autoincrement value generated; never an intermediate value. The only way I see to do this is rather painful: insert a new row that is a copy of the last row (to bump the autoincrement number for the table) and then update all the remaining rows (including the one you think of as the new one).

If the values are unique, I wonder about the wisdom of using a separate id column. Why not make the value column the primary key?

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
0

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)
Mahmoud Magdy
  • 662
  • 10
  • 13