CODEWITHSUNDEEP
mysql
pheromix
pheromix

Reputation: 19347

Cannot insert a row into a table with a select statement using the table

I want to insert a row into a table :

insert into menu(title, routerlink, icon, hassubmenu, parentid, alignement) 
values('Accès site', '/pages/reporting/accessite', 'reorder', 0, (select idmenu from menu where title = 'Reporting' and parentid = 0), 0);

When executed it gives the error You can't specify target table 'menu' for an update in FROM clause

So how to solve it ?

Upvotes: 0

Views: 160

Answers (1)

Akina
Akina

Reputation: 42728

You must use INSERT .. SELECT, not INSERT .. VALUES.

insert into menu ( title, 
                   routerlink, 
                   icon, 
                   hassubmenu, 
                   parentid, 
                   alignement ) 
select 'Accès site', 
       '/pages/reporting/accessite', 
       'reorder', 
       0, 
       ( select idmenu 
         from menu 
         where title = 'Reporting' and parentid = 0 /* LIMIT 1 */ ), 
       0;

You must understand that if the subquery returns more than one row then the query will fail. So you should add LIMIT 1 (with proper ORDER BY maybe) into it.

Upvotes: 1

Related Questions