[SOLVED] Insert new row with data computed from other rows

Insert new row with data computed from other rows

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.

Solution

You can just append that select (slightly modified as in "you don't need the as clauses") onto an insert. For example:

insert into MyTable (Id,Type,Value)
    select MyTable_A.Id, 'C', (A_Val + B_Val) from ...

assuming that your query is actually correct - I make no evaluation of that :-)

By way of further example,

insert into MyTable (Id,Type,Value)
    select Id+1000, 'C', Value from MyTable where Type = 'A'

would add the following rows:

+------+------+-------+
| Id   | Type | Value |
+------+------+-------+
| 1000 | C    |     1 |
| 1001 | C    |     2 |
| 1002 | C    |     5 |
+------+------+-------+