5

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.

Tyler McHenry
  • 74,820
  • 18
  • 121
  • 166
  • This will de-normalise your data. Why do you want to do this? What will happen when the A's and B's change? Can someone modify the C's? – Marcelo Cantos May 16 '10 at 13:48
  • The reason this is required is that while `C` is defined as `A+B`, and all entries in the tables currently have `A` and `B` values, there will be future entries where only `C` is known and where there's no way to determine the breakdown into `A` and `B` components. I realize that this means that I will have to implement external validation logic that ensures that `C = A+B` for insertions and updates on `Id` s where `A` and `B` *are* known. – Tyler McHenry May 16 '10 at 13:52

2 Answers2

4

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 |
+------+------+-------+
paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • Excellent! Simple, and even pre-emptively answered my followup question of "what if there are other columns in the table that I want to just take their default values?" – Tyler McHenry May 16 '10 at 14:11
2

Just add the following line before your select statement:

INSERT MyTable (Id, Type, Value)
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452