CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Update rows with data from the same table

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;

Upvotes: 1

Views: 132

Answers (4)

Christian Ammer
Christian Ammer

Reputation: 7542

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 |
+------+------+--------+---------+--------+

Upvotes: 1

Eugen Halca
Eugen Halca

Reputation: 1785

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

Upvotes: 1

rileyteige
rileyteige

Reputation: 21

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.

Upvotes: 0

Sonam
Sonam

Reputation: 3466

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

Upvotes: 0

Related Questions