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
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