mysql - Increment integer field by 1 starting from a certain row -

table1: id, field1(integer), field2, ...

as shown above field1 integer serialized 1 100

i enter new record field1 value 45 php form. given value exists in table (from 1 100)

**i want query store new record before existing 1 in table , increment field1 values starting second 45 **

thanks help!

i recommend against this. normally, should insert rows in order. if want sort, sort name, or relevant value.

if want reason have particular sort order, can in 2 update statements:

1: shift rows make room new row.

update yourtable set field1 = field1 + 1 field1 >= 45 

2: insert new row.

insert yourtable (field1) values (45) 

as can tell, can become inefficient, since updating rows. if table becomes larger, take more time insert rows, @ beginning.

if need can optimize little, instance, keeping gaps between rows. if have numbering in steps of 100, can insert row @ 4450, right between 4400 , 4500. need renumber when there no gap between records want insert new row.