mysql - Insert new row with data computed from other rows -


Let's say I have a MySQL table, which is called Matabal, which looks like this: 

  + - -  
 
 --- + - ---- + ------- + | ID | Type | Price | + ---- + ------ + ------- + | 0 | A. 1 | | 0 | B 1 | | 1 | A. 2 | | 1 | B 3 | | 2 | A. 5 | | 2 | B 8 | + ---- + ------ + ------- +

And for each id , I insert one I want to type C with the new line whose value is the sum of a and the value for the value of b Same id . The primary key in this table is (id, type) , so there is no question of duplication of the ID, type pair.

I can create rows with this query: 

  select MyTable_A.Id AS ID, type 'C' type, (A_well + B_VIL) value MyTable_A JOIN (SELECT Id as value SELECT Id, A_Val MyTable as WHERE type = 'A'), MyTable_A.Id as MyTable_A.Id as MyTable_A.Id as B_Val MyTable WHERE type = 'B')

give: 

  + ---- + ------ + ------- + | ID | Type | Price | + ---- + ------ + ------- + | 0 | C. 2 | | 1 | C. 5 | | 2 | C. 13. + ---- + ------ + ------- +

But the question is: I use this result to insert the resulting result How to do C rows in MyTable

There is a relatively simple way to do this with a query, or one of my stored Need to write procedure? And if the latter, guidance will be helpful, because I am not well aware of them.

You can only add that selection (is modified a bit like "you As sections are not required ") on a insert For example: 

  Insert in MyTable (id, type, value) from MyTable_A.Id, 'C', (A_Val + B_Val) ... ...  < Assuming that your query is actually true - I do not do any assessment on it: -) 
 
 Through further examples, 
 
 < Select the value from the code> MyTable (ID, Type, Value) I + 1000, 'C', MyTable where Type = 'A'  
 
 will add the following lines: 
 
  + ------ + ------ + ------- + | ID | Type | Price | + ------ + ------ + ------- + | 1000 | C. 1 | | 1001 | C. 2 | | 1002 | C. 5 | + ------ + ------ + ------- +

Comments

Post a Comment