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) );
posted on 20.06.2020 at 14:55
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 ) );
posted on 21.06.2020 at 11:39
0pnshow more
TimTim
share on facebookshare on twitter
2020 AnswerTabsTermsContact us
This site uses cookies to improve your experience, for analytics and ads. By continuing to browse this site you agree to our use of cookies.