How to add new values to a DB and update the rows in what they have coincidences?-CodePudding

I know that the title is very confuse, but i think with this example can help to ilustrated better the situation.

DATABASE:
--------------------
| time | a | b | c |
| 0    | 1 | 2 | 3 |
| 1    | 2 | 4 | 4 |
| 2    | 2 | 3 | 3 |
--------------------

NEW DATA:
--------------------
| time | a | b | c |
| 2    | 4 | 4 | 5 |
| 3    | 2 | 3 | 4 |
| 4    | 4 | 4 | 4 |
--------------------

Final DB:
--------------------
| time | a | b | c |
| 0    | 1 | 2 | 3 |
| 1    | 2 | 4 | 4 |
| 2    | 4 | 4 | 5 |
| 3    | 2 | 3 | 4 |
| 4    | 4 | 4 | 4 |
--------------------

I am using MySql as my local database, i am getting data from a external database every so often. I want to add the new data to the local DB and if the new data have the same "time" that any row in the local DB i want to update this row.

PD: I am using SqlAlchemy as my ORM

CodePudding user response：

If you use "time" as primary key, you can do something like:

INSERT INTO table (time, a, b, c) VALUES ("time", "a", "b", "c"),
                                         ("time", "a", "b", "c"),
                                         ("time", "a", "b", "c")
ON DUPLICATE KEY UPDATE a = VALUES(a),
                        b = VALUES(b),
                        c = VALUES(c)

ON DUPLICATE KEY UPDATE is the secret sauce. Whenever the primary key already exists, MySQL will update the record (instead of throwing an error). Keep in mind that this doesn't delete rows. It's like an upsert (create or update if exists).