I load data on daily basis from python into a mysql database and a table "temp_table". I want to join this into the "total_table" so that new values get added to the "total_table"

 -----Temp-table------- 
 ---------------------- 
| id | Price    | Name |
 ---- ---------- ------ 
| 3  | 1.000    | C    |
| 4  | 1.500    | D    |
| 5  | 1.200    | E    |
| 6  | 2.000    | F    |


 -----Total-table------ 
 ---------------------- 
| id | Price    | Name |
 ---- ---------- ------ 
| 1  | 1.100    | A    |
| 2  | 1.600    | B    |
| 3  | 1.000    | C    |
| 4  | 1.500    | D    |

result after joining

 -----Total-table------ 
 ---------------------- 
| id | Price    | Name |
 ---- ---------- ------ 
| 1  | 1.100    | A    |
| 2  | 1.600    | B    |
| 3  | 1.000    | C    |
| 4  | 1.500    | D    |
| 5  | 1.200    | E    |
| 6  | 2.000    | F    |

I have tried LEFT JOIN. Don't get any errors in python but my total-table is not updated

 SELECT * FROM total_table
            LEFT JOIN
            temp_table
            ON total_table.id = temp_table.id

CodePudding user response：

Hey I think you are looking for the UNION operator:

SELECT * FROM total_table
UNION
SELECT * FROM temp_table

CodePudding user response：

INSERT IGNORE INTO total_table
SELECT *
FROM temp_table

The IGNORE option will make it skip the rows in temp_table that duplicate the unique key in total_table.

CodePudding user response：

You can perform an UPSERT of the temp data.
Via an INSERT ON DUPLICATE KEY UPDATE statement.

Note that the target table does require a primary key or unique index for this.

INSERT INTO total_table
(id, Price, Name)
SELECT 
 id, Price, Name 
FROM temp_table tmp
ON DUPLICATE KEY UPDATE
  Price = tmp.Price
, Name = tmp.Name;

select * 
from total_table
order by id;

db<>fiddle here