Insert new row with data computed from other rows

Posted by Tyler McHenry on Stack Overflow See other posts from Stack Overflow or by Tyler McHenry
Published on 2010-05-16T13:41:32Z Indexed on 2010/05/16 14:00 UTC
Read the original article Hit count: 226

Filed under:
|
|

Suppose I have a MySQL table called MyTable, that looks like this:

+----+------+-------+
| Id | Type | Value |
+----+------+-------+
|  0 | A    |     1 |
|  0 | B    |     1 |
|  1 | A    |     2 |
|  1 | B    |     3 |
|  2 | A    |     5 |
|  2 | B    |     8 |
+----+------+-------+

And, for each Id, I want to insert a new row with type C whose Value is the sum of the type A and B values for the rows of the same Id. The primary key on this table is (Id, Type), so there's no question of duplication of Id,Type pairs.

I can create the rows I want with this query:

SELECT MyTable_A.Id AS Id, 'C' AS Type, (A_Val + B_Val) AS Value FROM 
       (SELECT Id, Value AS A_Val FROM MyTable WHERE Type='A') AS MyTable_A
  JOIN (SELECT Id, Value AS B_Val FROM MyTable WHERE Type='B') AS MyTable_B
    ON MyTable_A.Id = MyTable_B.Id

Giving:

+----+------+-------+
| Id | Type | Value |
+----+------+-------+
|  0 | C    |     2 |
|  1 | C    |     5 |
|  2 | C    |    13 |
+----+------+-------+

But the question is: How do I use this result to insert the generated type-C rows into MyTable?

Is there a relatively simple way to do this with a query, or do I need to write a stored procedure? And if the latter, guidance would be helpful, as I'm not too well versed in them.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query