1

What is the right way to write an sql that'll update all type = 1 with the sum of totals of rows where parent = id of row with type=1.

Put simply: update likesd set totals = sum of all totals where parent = id of row where type = 1 

"id"    "type"  "parent"    "country"   "totals"
"3"     "1"     "1"         "US"        "6"
"4"     "2"     "3"         "US"        "6"
"5"     "3"     "3"         "US"        "5"

Desired results

"id"    "type"  "parent"    "country"   "totals"
"3"     "1"     "1"         "US"        "17" ->6+6+5=17
"4"     "2"     "3"         "US"        "6"
"5"     "3"     "3"         "US"        "5"

I was trying with (and failed)

UPDATE  likesd a
        INNER JOIN (
        SELECT  parent, sum(totals) totalsNew
        FROM likesd
        WHERE b.parent = a.id 
        GROUP BY parent
        ) b ON a.id = b.parent
        SET a.totals = b.totalsNew;
Norman
  • 6,159
  • 23
  • 88
  • 141
  • What results did you get in your "Failure" – Elias Sep 27 '13 at 19:25
  • I would say, leave it as it is, and based on the child/parent relationship, show the total. That way you don't have a "calculated" field in some places and the math to get that calculated field in the rest. E.G. Leave it in the presentation layer. – Elias Sep 27 '13 at 19:26
  • @Elias. I get ` Unknown column 'b.parent' in 'where clause'` – Norman Sep 27 '13 at 19:30
  • Does ID 3 always get the total or if say id 5 is the parent should that get the total of just id's that have a parent of 5? – Josh Sep 27 '13 at 19:38
  • `Type = 1` will get the total. So if there are many `type=1` then all need to get updated with the sums() of their respective children. – Norman Sep 27 '13 at 19:42

4 Answers4

1

here is command that does what you want

update likesd as upTbl
        inner join
    (select 
        tbl.id, tbl.totals + sum(tbl2.totals) as totals
    from
        likesd tbl
    inner join likesd tbl2 ON tbl2.parent = tbl.id
    where
        tbl.type = 1
    group by tbl.id) as results ON upTbl.id = results.id 
set 
    upTbl.totals = results.totals;

tested on MySql 5.5

Eugen Halca
  • 1,775
  • 2
  • 13
  • 26
1

You can do this with the multiple table syntax described in the MySQL Reference Manual:

update likesd a, (select parent, sum(totals) as tsum
       from likesd group by parent) b
set    a.totals = a.totals + b.tsum
where  a.type = 1 and b.parent = a.id;

The query updates one row and results in:

+------+------+--------+---------+--------+
| id   | type | parent | country | totals |
+------+------+--------+---------+--------+
|    3 |    1 |      1 | US      |     17 |
|    4 |    2 |      3 | US      |      6 |
|    5 |    3 |      3 | US      |      5 |
+------+------+--------+---------+--------+
Christian Ammer
  • 7,464
  • 6
  • 51
  • 108
0

Please try the following query:

with update_cte(parent,totals)
as(select parent, sum(totals)totalsNew
FROM likesd where type=1 group by parent)
update a
set a.totals=b.totals
from likesd a join update_cte b on a.id=b.parent
Sonam
  • 3,406
  • 1
  • 12
  • 24
0
update likesd
set totals = (
        select a.childTotals
        from (
            select sum(totals) as childTotals
            from likesd
        ) as a
    )
where id = parent and type = 1;

Edit: As per MySQL Error 1093 - Can't specify target table for update in FROM clause, using an implicit temp table to allow an update to the same table used in a nested SELECT statement.

Community
  • 1
  • 1
rileyteige
  • 21
  • 2