updating several rows based on subquery in mysql

Question

Here is a simplified table structre for what I am trying to do:

T1:
id,uid,group,value1
1,1,A,3
2,1,B,4
3,2,A,6
4,2,B,7

now I want that any uid that has both a row for group A and group B the value assosicated with group B will be added to the value in the row associated with Group B.

so the result for the above would be
1,1,A,7 (3+4)
2,1,B,4
3,2,A,13 (6+7)
4,2,B,7

there might also be uids for which only group A exists or only groups B exists in which case no changes t those lines should take place.

What I did so far is that I created a query that has one row for any row that needs to be updated:

id,uid,newval
1,1,7
3,2,13

The subquery is somewhat complex and joins the table that needs to be updated with it self.

now how do I use this to update the original table? (or how do I do it some other way)?

thanks.

therealmitchconnors · Accepted Answer · 2011-06-07 18:27:44Z

1

Update T1
set Value1 = T1.Value + T2.Value
from T1 inner join T1 as T2 on T1.uid = T2.uid
where T1.group = "A" and T2.group = "B"

answered Jun 7, 2011 at 18:27

therealmitchconnors

2,7801 gold badge20 silver badges36 bronze badges

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

2 Comments

epeleg Over a year ago

how would this go If I want an T1 to be the alias for MyTableWithLongName ? and what If I need to update two fields at once ?

therealmitchconnors Over a year ago

Update MyTableWithLongName set Value1 = T1.Value + T2.Value, Value2 = x from MyTableWithLongName as T1 inner join MyTableWithLongName as T2 on T1.uid = T2.uidwhere T1.group = "A" and T2.group = "B"

Collectives™ on Stack Overflow

updating several rows based on subquery in mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related