I feel stupid for asking, but I have a small problem with my SQL.

Lets say, you have a Table with the following columns:

id (= The id of the post)
replyto (= The id of the post this post replies to)
replies (= The count of replies)

What I want to do, make a SQL query, that counts how many replies a post has and stores it in replies.

The Problem I'm having is that the id of the post and the reply count are in the same table, if it were different tables it would work, like how I count the like count.

UPDATE posts
SET likes = (SELECT COUNT(likes.id) FROM likes WHERE postid = posts.id)

But because I store replies as a post that have a set replyto value and a store in the same table, the above code does not work.

CodePudding user response：

Table alias (the AS statement) allows you to refer to a table by an alias you give it, thus enabling you to compare values between same table.

Wrong answer:

UPDATE posts AS table1
SET replies = (SELECT COUNT(1) FROM posts AS table2 WHERE table2.replyto = able1.id)

Correct answer: (with caveats)

It seems you can't use update on same table you are selecting from. The workaround is to wrap the offending query inside another.

UPDATE posts AS table1
SET replies = (SELECT counter FROM 
    (SELECT COUNT(1) AS counter FROM posts AS table2 WHERE table2.replyto = table1.id) AS table3
)

This will work. But on some situations this might throw you the same error. Read more https://stackoverflow.com/a/45498/3807365