0

I have an issue whereby a query that works perfectly fine in Postgres fails in MySQL. I have checked out other stack questions and googled the error but still cannot quite figure out what is wrong.

insert into queue (name, id) values ('name',(select max(id)+1 from queue));

Now it just fails with the following error

ERROR 1093 (HY000): You can't specify target table 'queue' for update in FROM clause
Maciej Cygan
  • 5,351
  • 5
  • 38
  • 72
  • 2
    It would seem SOoooo much simpler to make the `id` column a `Auto Increment` column and then this would happen automatically and be guaranteed to be right – RiggsFolly Mar 11 '22 at 15:17
  • @Jens it is actually a valid name - I've corrected the statement to reflect real name – Maciej Cygan Mar 11 '22 at 15:19
  • @RiggsFolly indeed - but have to work with what I have at the moment – Maciej Cygan Mar 11 '22 at 15:20
  • 1
    That duplicate posting is rather abstruse. I'd sure like to see the simple answer to this question exactly as posted. And to me, certainly the top answer isn't all that helpful. I can see the second answer `INSERT INTO tableA SET fieldA = (SELECT max(x.fieldA) FROM tableA x)+1;` is close, but not exactly what is asked here. – zipzit Mar 11 '22 at 15:28
  • @zipzit - indeed... – Maciej Cygan Mar 11 '22 at 15:31
  • @MaciejCygan if it were me... I'd use the hints from [the reference](https://stackoverflow.com/questions/205190/select-from-same-table-as-an-insert-or-update), figure out a workable solution, rewrite this question again, and submit your own answer immediately. Then delete the original. There is no good way to downvote a "This question already has an answer..." submit that I can see. This is not a duplicate.. very similar, yes, but not a duplicate. – zipzit Mar 11 '22 at 15:37

0 Answers0