I'm very bad at joints and often appeal to subqueries. So when I'm trying to update likes field of article
table from articlelikes
, I use this query:
UPDATE article SET likes =
(SELECT COUNT(*) from articlelike WHERE article_id IN
(SELECT id FROM article AS f WHERE f.creator_id= 42 ));
but I get this error:
ERROR 1093 (HY000): You can't specify target table 'article' for update in FROM clause
I know that there are similar questions like this, but I could not apply their answers to my case. When I run the suggested queries I get this error:
ERROR 1048 (23000): Column 'likes' cannot be null when I run the suggested query.