Update block columns from table1 to table2

Question

Tables:

mysql> select * from table1; +------+------+------+ | no | nm | unit | +------+------+------+ | 1 | ABC | 10 | | 2 | ACX | 20 | | 3 | AYU | 30 | +------+------+------+ 3 rows in set (0.01 sec) 

 mysql> select * from table2; +------+------+------+ | no | nm | unit | +------+------+------+ | 1 | ABC | 40 | | 2 | ACX | 20 | +------+------+------+ 2 rows in set (0.00 sec) 

Required Conclusion:

 mysql> select * from table2; +------+------+------+ | no | nm | unit | +------+------+------+ | 1 | ABC | 50 | | 2 | ACX | 40 | | 3 | AYU | 30 | +------+------+------+ 3 rows in set (0.00 sec) 

Answer 1

If you want to update Table2 as needed, then you need to update existing rows first, and then insert new rows from Table1 :

You can update the device using JOIN :

 UPDATE Table2 t2 JOIN (SELECT nm, SUM(unit) unit FROM ( SELECT * FROM Table1 t1 UNION ALL SELECT * FROM Table2 t2 ) tbl GROUP BY nm ) tbl1 ON t2.nm = tbl1.nm SET t2.unit = tbl1.unit; 

Then you can add rows from table 1 that are not in table 2 (e.g. nm=AYU )

 INSERT INTO Table2 SELECT t1.`no`, t1.`nm`, t1.`unit` FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.nm = t2.nm WHERE t2.nm IS NULL; 

Output:

 SELECT * FROM Table2; | NO | NM | UNIT | |----|-----|------| | 1 | ABC | 50 | | 2 | ACX | 40 | | 3 | AYU | 30 | 

See this SQLFiddle

Remember to update the table first. Otherwise, it will duplicate units.

Answer 2

I looked around and saw this answer , which, in my opinion, was more reasonable that arithmetic should be performed outside of database manipulations. But if you want to use SQL for this, I will edit it in this answer later.

Answer 3

If you just want to select data from two tables as needed, then try the following:

 SELECT MIN(no) NO, nm, SUM(unit) unit FROM ( SELECT * FROM Table1 t1 UNION ALL SELECT * FROM Table2 t2 ) tbl GROUP BY nm; 

Output:

 | NO | NM | UNIT | |----|-----|------| | 1 | ABC | 50 | | 2 | ACX | 40 | | 3 | AYU | 30 | 

See this SQLFiddle

Answer 4

select

no, nm sum (unit)

of

(

select * from table1

unification of all

select * from table2

) as the time group no, nm;