I struggled with the following quite some time and I do not seem to find the right solution. Even with examples here I am not able to solve it. Could you please help me to rewrite the query to see what I have done wrong?
I will put the query based on what I try to achieve:
insert into categorylist (SONGID, CATEGORYID, SORTID)
VALUES (
select songid, '784' as categoryid, sortid from categorylist
where categoryid = '781' and songid not in
(select songid from categorylist
where categoryid in (651, 514, 517, 731, 746,658,655,522, 525,775))
)
The select statement works, but when I add the insert part I got a Error 1064.
As an alternative I try to rewrite it, to update the categoryid:
update categorylist
set categoryid = '784'
where categoryid = '781' and songid not in
(select songid from categorylist where categoryid in
(651, 514, 517, 731, 746,658,655,522, 525,775))
Now I got an: errorcode 1093: You can't specify target table 'categorylist' for update in FROM clause
I have tried serveral solutions here and found elsewhere, but I can not work around it. Maybe it is not possible and is a temp db needed as stated in You can't specify target table '' for update in FROM clause
Thank you very much for your help.
