MySQL: Update same row multiple times in one query with values from another table

Question

Given the following table structures:

T1:

T2:

I need to add the value of t2.b to the value of t1.a where t2.t1_id = t1.id.

A simple update with a join like the following isn't working:

UPDATE t1
  JOIN t2 
    ON t2.t1_id = t1.id
   SET t1.a = t1.a + t2.b
 WHERE t2.id IN(1,2)

Expected T1 result (adds 1 and 2 to t1.a = 1; 3 to t1.a = 2):

Actual T1 result (only adds 1 to t1.a = 1; 3 to t1.a = 2):

At the moment I'm looking at a select that computes the full sum of values to be added using a group by, and then that result set is joined to the update... This seems like overkill for something so simple though! Does anyone have an elegant and efficient solution?

I think moving the join into a subselect at where clause should do the trick. — frlan
– frlan, Commented Jul 8, 2014 at 18:57
Problem is you have a one to many relationship from T1 - T2. You would only be adding an arbitrary single row from table 2 to the value from table 1. I can't think of a straight Join method of doing this off the top of my head. — Jestep
– Jestep, Commented Jul 8, 2014 at 19:08

Jestep · Accepted Answer · 2014-07-08 19:20:56Z

1

Came up with this real quickly.

UPDATE T1 AS t
JOIN (
    SELECT
        t1_id, SUM(b) AS sum_total
    FROM
        T2
    WHERE
        t1_id IN (1, 2)
    GROUP BY
        T2.t1_id
) AS t2 ON t.id = t2.t1_id
SET  t.a =  t.a + t2.sum_total;

answered Jul 8, 2014 at 19:20

Jestep

9852 gold badges8 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

whycodewhyyyy Over a year ago

Yeah that's the structure I had come to as well, was wondering if there's a 'nicer' way. Thanks!

Collectives™ on Stack Overflow

MySQL: Update same row multiple times in one query with values from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related