update an id to max+1 in current table? (MySQL)
UPDATE jobs j1
INNER JOIN jobs j2 ON j1.worker_id &lt;= j2.worker_id 
LEFT OUTER JOIN jobs j3 ON j2.worker_id &lt; j3.worker_id 
SET j1.worker_id = j2.worker_id + 1 
WHERE j1.worker_id = 3 AND j3.worker_id IS NULL;



j1 is the row you want to change, based on the condition in the WHERE clause.
j2 is the set of rows with a worker_id greater or equal to j1.
j3 is the set of rows with a greater worker_id than j2 (when j2 has the greatest worker_id in the table, there is no row for j3, so j3.* will be NULL).
j2 therefore has the greatest worker_id, so use its value + 1.


Demo:

mysql&gt; create table jobs (id serial primary key, worker_id int);
mysql&gt; insert into jobs (worker_id) values (1), (2), (3), (4), (5);

mysql&gt; select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         3 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+

mysql&gt; update jobs j1 inner join jobs j2 on j1.worker_id &lt;= j2.worker_id 
 left outer join jobs j3 on j2.worker_id &lt; j3.worker_id 
 set j1.worker_id = j2.worker_id + 1 
 where j1.worker_id = 3 and j3.worker_id is null;

mysql&gt; select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+


It works regardless of the value we want to change:

mysql&gt; update jobs j1 inner join jobs j2 on j1.worker_id &lt;= j2.worker_id 
 left outer join jobs j3 on j2.worker_id &lt; j3.worker_id 
 set j1.worker_id = j2.worker_id + 1 
 where j1.worker_id = 5 and j3.worker_id is null;

mysql&gt; select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         7 |
+----+-----------+


And it works even if we're changing the row that already has the highest value in the table:

mysql&gt; update jobs j1 inner join jobs j2 on j1.worker_id &lt;= j2.worker_id 
 left outer join jobs j3 on j2.worker_id &lt; j3.worker_id 
 set j1.worker_id = j2.worker_id + 1 
 where j1.worker_id = 7 and j3.worker_id is null;

mysql&gt; select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         8 |
+----+-----------+