Geek Answers Handbook

Insert a new row with data calculated from other rows.

Suppose I have a MySQL MyTable table 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 a type Cwhose Valueis the sum of the values ​​of the type Aand Bfor the same rows Id, the Primary key in this table (Id, Type), so there is no question of duplicating Id pairs of type.

I can create the lines 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

Donation:

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

But the question arises: how to use this result to insert generated type strings Cin MyTable?

? , , .

+5
2

( , " as" ) . :

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

, - : -)

,

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

:

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

select:

INSERT MyTable (Id, Type, Value)
+2

More articles:


All Articles