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) );
0
give a positive ratinggive a negative rating
20 Jun 2020 at 02:55 PM
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 ) );
0
give a positive ratinggive a negative rating
21 Jun 2020 at 11:39 AM
Tim
Share on FacebookShare on TwitterShare on LinkedInSend email
x
x
Follow us on Facebook
2022 AnswerTabsTermsContact us