update an id to max+1 in current table? (MySQL)

Question

I've got a 'jobs' table where each row has a 'worker_id' that is either zero (job is not being worked on) or some unique ID that needs to be incremental.

Basically, when assigning this worker_id, I want to give it the first next available worker_id.

Here's what I'm trying:

UPDATE jobs SET worker_id=(SELECT MAX(worker_id)+1 FROM jobs) WHERE jobname='Abc'

The idea behind this is: if the current highest worker_id in my jobs table is 14, this query would set the worker_id for my job 'Abc' to 15.

But instead, this results in an error:

#1093 - You can't specify target table 'jobs' for update in FROM clause

What should I do?

FYI, the output of SHOW CREATE TABLE jobs is:

CREATE TABLE jobs ( jobname varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, worker_id int(11) NOT NULL DEFAULT '0', email varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, customer_id int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8

(I just removed the ` chars around table and column names for pasting as code here on StackExchange)

Bill Karwin · Answer 1 · 2014-05-23T00:24:09.650

UPDATE jobs j1
INNER JOIN jobs j2 ON j1.worker_id <= j2.worker_id 
LEFT OUTER JOIN jobs j3 ON j2.worker_id < 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> create table jobs (id serial primary key, worker_id int);
mysql> insert into jobs (worker_id) values (1), (2), (3), (4), (5);

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

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

mysql> 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> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id 
 left outer join jobs j3 on j2.worker_id < j3.worker_id 
 set j1.worker_id = j2.worker_id + 1 
 where j1.worker_id = 5 and j3.worker_id is null;

mysql> 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> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id 
 left outer join jobs j3 on j2.worker_id < j3.worker_id 
 set j1.worker_id = j2.worker_id + 1 
 where j1.worker_id = 7 and j3.worker_id is null;

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

update an id to max+1 in current table? (MySQL)

1 Answers1