Help for a Sql statement

Question

Hy at all, today is the day of ...question. I've a single table, with a relation master-detail like this:

RecordID    MasterID   Field1 Field2 .... NrDetail
1           0          xxx     yyyy       1
2           0          aaaa     bbbb      2
3           1          hhhhh   ssss       0
4           2          eee     sssss      0
5           2          jjj      hhhh      0

As you can see, NrDetail contain the total of "child record". Unfortunately, i've to create this field... and i would like to write it in my table. So my SQL question is: how to do this type of SQL to write the field NrDetail ?

Something like:

UPDATE table SET NrDetail= 
  (SELECT COUNT(*) as Total FROM table WHERE MasterID= RecordID) 

But i think there's some mistake...

Thank you in advance !

Answer 1

I think that you have forgetten to specify which MasterID you want to compare with which RecordID. How about:

UPDATE table t1 SET NrDetail= 
  (SELECT COUNT(*) as Total FROM table t2 WHERE t1.MasterID=t2.RecordID) 

Answer 2

UPDATE table
SET NrDetail =  (
                Select Count(*)
                FROM table t2 
                Where t2.RecordID = table.MasterID
                )

In an update statement, when you want to reference the table being updated, you need to use the full reference for columns(tablename.columnname, or ideally schema.tablename.columnname). If you are using the same table in a subquery, you need to alias the table in the subquery but again use the full reference for the outer table.

ADDITION Since you mentioned that you are using MySql, you could try something like so:

Update post
    Join    (
            Select p1.idpadre, Count(*) Total
            From post p1
            Group By p1.idpadre
            ) Z
        On Z.idpadre = post.idpost
Set post.NrDetail = Z.Total