CODEWITHSUNDEEP
mysqlsqlsql-update
Norman
Norman

Reputation: 6365

mySql update with the sum() of rows

    "id"    "type"  "parent"    "country"   "votes" "perCent"
    "1"     "1"     "0"         "US"        "100"       "0"
    "2"     "1"     "0"         "US"        "50"        "0"
    "3"     "100"   "0"         "US"        "150"       "0" ->150 = sum(votes) where type = 1 and country = country
    "4"     "1"     "0"         "SE"        "50"        "0"
    "5"     "1"     "0"         "SE"        "25"        "0"
    "6"     "100"   "0"         "SE"        "75"        "0" ->75 = sum(votes) where type = 1 and country = country

I'm trying to update type=100 with the totals of all type=1 for their respective countries.

I've been struggling with this sql and seem to going nowhere. Basically, what I'm trying to do is update votes where type=100 with the sum of type = 1 for their respective countries. I've been trying to tweak this, but seem to be failing completely. Can you pls help?

UPDATE likes p
JOIN likes h
  ON p.country = h.country
    AND (p.type=1) AND h.type=1
SET p.votes=sum(h.votes) where p.type=100;

Upvotes: 0

Views: 75

Answers (1)

John Woo
John Woo

Reputation: 263723

UPDATE  tableName a
        INNER JOIN
        (
            SELECT  country, SUM(votes) totalVotes
            FROM    tableName
            WHERE   type = 1
            GROUP   BY country
        ) b ON a.country = b.country
SET     a.votes = b.totalVotes
WHERE   a.type = 100

Upvotes: 2

Related Questions