CODEWITHSUNDEEP
mysqlsqlsql-serversum
mitch9191
mitch9191

Reputation: 1

SQL: Sum of multiple rows to update initial table

The below keeps giving me errors, I have worked out it's the group by however I have no indication of what is wrong/ how to fix.

t1

    c1 c2

    1 -

    2 -

t2

    c1 c2

    1 - 3

    1 - 2

    2 - 2

    2 - 2

    UPDATE T1 a, t2 b SET a.c2 = sum(b.c2)
    GROUP BY b.c1 HAVING b.c1 = a.c1;

Upvotes: 0

Views: 52

Answers (3)

japzdivino
japzdivino

Reputation: 1746

How about this:

UPDATE a SET a.c2 = sum(b.c2)
FROM T1 a INNER JOIN t2 b ON a.c1 = b.c1     
GROUP BY b.c1

Upvotes: 0

Vance
Vance

Reputation: 897

Try this one: 

      UPDATE T1
      set a.c2= b.c2 from T1 a inner JOIN 
     (select c1, sum(c2) from T2 group by c1) b on a.c1 = b.c1;

Upvotes: 0

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

You can try this:

UPDATE T1
SET c2 = (SELECT SUM(c2) FROM T2 WHERE c1 = T1.c1)

Upvotes: 1

Related Questions