INSERT OR REPLACE only specific values?

Question

I have a table:

    DATE        WEATHERSTATION    TEMPERATURE        RAINFALL
1975-01-10           B81A             25                0
1975-01-11           K174             33                16
1975-01-12           ZG4N             18                3

I build this table by first processing an input that contains the date and temperature, and then an input that contains the date and rainfall. Sometimes, there are dates in the rainfall data that don't exist in the temperature data (for example, if a weather station started recording rainfall in 1950 but temperatures in 1970 -- it happens).

What I'd like to do is say:

If there's already a row with this date, update value X (which can be temperature or rainfall depending on the program);
If there isn't a row with this date, create it.

Obviously, I can do this within the program itself, but I think doing it in the SQL would be more efficient and sensible.

If I use INSERT OR REPLACE INTO weatherdata (date, weatherstation, temperature), because that deletes the rainfall value. What I'd like is INSERT OR UPDATE INTO WEATHERDATA, but that doesn't appear to exist; I've Googled it, and read StackOverflow posts about it, but nothing has really helped me.

What would be the proper thing to do in this situation?

(The database system is SQLite, if that matters.)

possible duplicate of [INSERT IF NOT EXISTS ELSE UPDATE?](http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update) — Mad Physicist, Feb 07 '15 at 07:45
See this: http://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update. Use a select to get the real value of the column you do not want to replace. — Mad Physicist, Feb 07 '15 at 07:45
Better yet, see this: http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace — Mad Physicist, Feb 07 '15 at 07:46

score 1 · Accepted Answer · edited May 23 '17 at 12:20

Doing it in SQL would be more efficient only if you had a client/server database and wanted to minimize network overhead. But SQLite is an embedded database, it is a library that is linked into your program; mixing SQL with your program's logic does not lose anything.

Doing it in SQL would not be sensible (it's possible, but reading and deleting the old row and then inserting a new row is way less efficient).

Just do the logic in your program:

db.execute("UPDATE ...")
if db.affected_rows == 0:
    db.execute("INSERT ...")

INSERT OR REPLACE only specific values?

1 Answers1