Menu
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.
I've tried:
INSERT INTO table (field names, `field_1`)
VALUES (values, '(SELECT MAX(field_1) FROM table)');
I get '0' in the field_1.
I know I can do it in separate queries.
Is there a way to perform this action with one query? I mean one call from php.
I have an auto-increment field 'id' and I want to add 'position' field. I want to be able to make changes in position but the new item will always have highest position
899
Auto Increment is a field used to generate a unique number for every new record added into a table. This is generally used for the primary key column as it becomes easy for the developers to automatically generate a unique number for every new record.
Here's the syntax of ALTER TABLE statement, ALTER TABLE table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY; In the above statement, you need to specify the table_name and column_name. Here's the SQL statement to add AUTO INCREMENT constraint to id column.
To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment. Inserting records into the table. To display all the records.
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 Position.
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.
188
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
