How many of us have had to increment a MySQL field and done a select query to get the current value, use PHP or another language to increment it, then turn right back around and run an update query.
This little snippet of code will save you two of those steps, and save precious time doing it.
MySQL, being as beautifully simple and functional as it is, will let you increment a field via SQL, no reason to use another language to do it. The snippet below is the query in it its simplest terms.
UPDATE
table_name
SET
field_to_increment = field_to_increment + 1
WHERE
( id_field = 'some_id' );
What can I do to make this work if the field is unique integer? As soon as the first one attempts to increment, I get a duplicate entry error.
The a field in one of your rows must already have the id it is trying to increment to, You could try to get around it by doing your UPDATE query in reverse ORDER… try adding something like:
ORDER BY id_field DESC that way it will be adding x to the highest number always and you should avoid cascading duplicates.
Why would you be incrementing a unique integer (auto-increment?) field?
It doesn’t have to be an auto-increment field to require a unique value (I think?). Just a primary key field, which are most often set to auto-increment for obvious reasons.
Thanks Doug, the ORDER BY did it!
Regarding Emily’s question: In my case the integer field relates to previous employments in the user’s CV. If the user changes job, then all previous jobs need the integer to increment. Current Job becomes Most Recent Job, Most Recent Job becomes Most Recent – 1, and so on.