1

We have table t_post and table t_post_like.

t_post has a column called like_count, which stores how many likes it got.

Every time a user likes the post, a new row will created in t_post_like, each row contains the post's id in column post_id. the like_count field in t_post will increase by 1 as well.

Now we wish to correct the like_count in t_post with this SQL that I found in this answer:

update p
set p.like_count = l.like_count_by_post 
 from t_post p inner join 
(
    select post_id, count(1) like_count_by_post
    from t_post_like
    group by post_id
) l
on p.id = l.post_id;

But we got error right syntax to use near 'from t_post c inner join..., Is the update set from syntax not supported in MySQL?

fall
  • 984
  • 11
  • 33

1 Answers1

3

MySQL does support an update join syntax, but it would look more like this:

UPDATE t_post p
INNER JOIN
(
    SELECT post_id, COUNT(*) like_count_by_post
    FROM t_post_like
    GROUP BY post_id
) l
    ON l.post_id = p.id
SET
    like_count = l.like_count_by_post;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • So I guess the syntax I used was considered not recognizable by MySQL then? – fall Jan 25 '21 at 01:33
  • 2
    [You were using SQL Server update join syntax](https://stackoverflow.com/questions/982919/sql-update-query-using-joins), apparently. – Tim Biegeleisen Jan 25 '21 at 01:34
  • 1
    I had no idea it was a SQL server syntax. I really hope that we can learn the syntax once and apply to all SQL databases. Instead of learning a syntax for each DB. – fall Jan 25 '21 at 12:25
  • 2
    @fall yeah, this has been a dream for most sql developers for a long-long time... – Shadow Jan 25 '21 at 12:36