UPDATE restriction on SubQuery Table

[James Cobban]

I have a spot where I need to set an integer field to 1 more than the highest value of that field in the table. Bear in mind that I did not design this table. I have got the command to work with the current level of MySQL:

INSERT INTO tblIR (IDIR, many other fields)
SELECT 1 + COALESCE(MAX(IDIR), 0), many other values FROM tblIR

I do the SELECT to get MAX(IDIR) in the INSERT so the update is atomic. The COALESCE is so the code will work for the very first record inserted into the table.

But this syntax does not strike me as intuitive because that first field is the only one whose value comes from tblIR. That is all of the other values are constants passed in the parameter array to the prepared statement execute. But MySQL 5.6 and above rejects the use of a sub-query in this situation with the unexplained declaration "You cannot update a table and select from the same table in a subquery." This INSERT used to read:

INSERT INTO tblIR (IDIR, many other fields)
(1 + COALESCE((SELECT MAX(IDIR) FROM tblIR), 0), other values)

But that now fails with a 1093 error code.

So what does INSERT have against sub-queries? I honestly do not understand the difference between the two statements. I wish the documentation explained why this restriction was added.