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

I am receiving the following error notification when I try to use SQL INSERT and SELECT together:

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

How can I fix this to make it work correctly ? I am using the following SQL:

INSERT INTO items ( name, description, price, quantity, position ) VALUES ( 'Item 1', 'Description 1', 25, 10, (SELECT (MAX(position)+1) FROM items) );
20 Jun 2020 at 02:55 PM
0pnshow more
StorStor
Hi,

To use INSERT and SELECT statements together, you have to also use AS command to create a table alias, because you are reading and inserting the data from the same table. The similar situation will happen, when there would be UPDATE and SELECT used together.

SQL should look like:

INSERT INTO items ( name, description, price, quantity, position ) VALUES ( 'Item 1', 'Description 1', 25, 10, (SELECT (MAX(secondtable.position)+1) FROM items AS secondtable ) );
21 Jun 2020 at 11:39 AM
0pnshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook & Twitter
2021 AnswerTabsTermsContact us