You can't specify target table 'tblempleave' for update in FROM clause

Question

I am using the below code to update the employee id from 123456 to 00123456 but it returns error.

MySQL Database Error: You can't specify target table 'tblempleave' for update in FROM clause

My query:

update tblempleave 
   set fldempid = concat('00',fldempid) 
 Where fldempid in (select fldempid from tblempleave);

What is the `where` clause doing? What you want to do? – bansi Jul 08 '13 at 04:35 — bansi, Jul 08 '13 at 04:35
As it currently stands, the WHERE clause is superfluous. – Ja͢ck Jul 08 '13 at 04:36 — Ja͢ck, Jul 08 '13 at 04:36

score 1 · Answer 1 · answered Jul 08 '13 at 04:36

Looks like you want to update every rows of the table tblempleave. If this is true then just remove the WHERE clause:

UPDATE tblempleave 
   SET fldempid = CONCAT('00',fldempid);

If you want to update records between some range (e.g. 1 to 100) you can use BETWEEN...AND operator like this:

UPDATE tblempleave 
   SET fldempid = CONCAT('00',fldempid)
 WHERE fldempid BETWEEN 1 AND 100;

score 1 · Answer 2 · answered Jul 08 '13 at 04:48

1

Everyone else here (so far) is right: you don't need that where to update all rows.

Specifically, you don't need a subquery for the WHERE clause, which is what the DB complains about: you can't modify the same table you're reading rows from.

answered Jul 08 '13 at 04:48

LexLythius

1,904
1
12
20

score 1 · Answer 3 · edited May 23 '17 at 12:21

The reason your UPDATE is failing is because you're referencing the same table that is targeted for UPDATE, which causes a conflict, as changes may occur in the data returned from the nested subquery in the process of updating the outer table, thus producing unpredictable results.

As others have already pointed out in the comments, the Nested Select in the WHERE clause also happens not to be doing anything, because it's selecting all IDs from the same table.

In addition to the above noted problems, it's never advisable to use a subquery with MySQL, as they perform terribly (as a they are executed for each row in the outer query, potentially millions of times).

Assuming you need a where clause at all, you would do better to rewrite the above as a JOIN, (and potentially as a derived table) as necessary.

You can't specify target table 'tblempleave' for update in FROM clause

3 Answers3