-1

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.

JojocraftTv
  • 1
  • 1
  • 1
    it is very unclear, what you want you refer to tables that we know nothing about and data we also don't know. see [mre] with all tables and data and wanted result – nbk Jul 07 '22 at 17:18

2 Answers2

1

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

IT goldman
  • 14,885
  • 2
  • 14
  • 28
  • Can you please possibly add a [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f06da6ae13ab0519711bda706d06c100) too in the answer. I too tried but this doesn't seem to work. – Pankaj Jul 07 '22 at 17:45
  • Your fiddle is great, I'll try and fix my answer – IT goldman Jul 07 '22 at 17:50
0

You can't use teh same table, but ou can make a temporary onbe aned use that

UPDATE posts 
SET replies =  
    (SELECT COUNT(1) AS counter FROM (SELECT * FROM posts) AS t2 WHERE t2.replyto = posts.id
)
nbk
  • 45,398
  • 8
  • 30
  • 47