0

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.
Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
hjiw
  • 13
  • 6
  • Hi hjiw, welcome to Stack Overflow! As it stands this question does not have enough details for us to help you. Why can't you apply the answers to the question you linked? What happens when you try? Feel free to [edit](https://stackoverflow.com/posts/65237928/edit) to add more details to your question. – Pika Supports Ukraine Dec 11 '20 at 00:14
  • Hi Pika, I added comment why the answer does not work. basically I get `ERROR 1048 (23000): Column 'likes' cannot be null` when I run the suggested query. – hjiw Dec 11 '20 at 15:55
  • Thank you for the additional information! On Stack Overflow, [comments are meant to be temporary](https://meta.stackexchange.com/questions/19756/how-do-comments-work/19757#19757), so I have transferred the information from your comment to the original question. – Pika Supports Ukraine Dec 14 '20 at 16:57

1 Answers1

1

MySQL doesn't allow that syntax. You need to use a JOIN instead:

UPDATE article a LEFT JOIN
       (SELECT al.article_id, COUNT(*) as num_likes
        FROM articlelike al JOIN
             article a
             ON a.id = al.article_id
        WHERE a.creator_id = 42
        GROUP BY al.article_id
       ) aa
       ON a.article_id = aa.article_id
    SET a.likes = aa.num_likes;

Presumably, if there is no match, you want to set the value to 0, so use coalesce():

SET a.likes = COALESCE(aa.num_likes, 0);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Isn't there a way to use subqueries by a trick like this ? https://stackoverflow.com/a/45498/14795411 – hjiw Dec 10 '20 at 16:02
  • @hjiw . .. There is no need to use subqueries with any tricks. The MySQL documentation is pretty clear that referring to the table being modified is not supported in `update` and `delete`. Even if a "trick" seems to work, I'm a fan of paying attention to what the database says can be done. – Gordon Linoff Dec 10 '20 at 16:07
  • Thanks for the tip. I get `ERROR 1048 (23000): Column 'likes' cannot be null`. How can I fix this? – hjiw Dec 11 '20 at 15:54