There is a table with an int field - field_1.
I want to insert a new row.
The field_1 value will be Maximum value from all the entries plus one.
INSERT INTO table (field names, `field_1`)
VALUES (values, '(SELECT MAX(field_1) FROM table)');
Whatever it is that you are trying to do, it will not work, because it is not guaranteed to be atomic. So two instances of this query executing in parallel are guaranteed to mess each other up at some random point in time, resulting in skipped numbers and duplicate numbers.
The reason why databases offer auto-increment is precisely so as to solve this problem, by guaranteeing atomicity in the generation of these incremented values.
(Finally, 'Auto Increment Manually' is an oxymoron. It is either going to be 'Auto Increment', or it is going to be 'Manual Increment'. Just being a smart ass here.)
EDIT (after OP's edit)
One inefficient way to solve your problem would be to leave the
Position field zero or NULL, and then execute
UPDATE table SET Position = Id WHERE Position IS NULL. (Assuming
Id is the autonumber field in your table.)
An efficient but cumbersome way would be to leave the
Position field NULL when you have not modified it, and give it a value only when you decide to modify it. Then, every time you want to read the
Position field, use a
CASE statement: if the
Position field is NULL, then use the value of
Id; otherwise, use the value of
EDIT2 (after considering OP's explanation in the comments)
If you only have 30 rows I do not see why you are even trying to keep the order right on the database. Just load all rows in an array, programmatically assign incrementing values to any
Position fields that are found to be NULL, and when the order of the rows in your array changes, just fix the
Position values and update all 30 rows in the database.