issues: 521868864
This data as json
| id | node_id | number | title | user | state | locked | assignee | milestone | comments | created_at | updated_at | closed_at | author_association | pull_request | body | repo | type | active_lock_reason | performed_via_github_app | reactions | draft | state_reason |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 521868864 | MDU6SXNzdWU1MjE4Njg4NjQ= | 66 | The ".upsert()" method is misnamed | 9599 | closed | 0 | 15 | 2019-11-12T23:48:28Z | 2019-12-31T01:30:21Z | 2019-12-31T01:30:20Z | OWNER | This thread here is illuminating: https://stackoverflow.com/questions/3634984/insert-if-not-exists-else-update The term `UPSERT` in SQLite has a specific meaning as-of 3.24.0 (2018-06-04): https://www.sqlite.org/lang_UPSERT.html It means "behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint". The syntax in 3.24.0+ looks like this (confusingly it does not use the term "upsert"): ```sql INSERT INTO phonebook(name,phonenumber) VALUES('Alice','704-555-1212') ON CONFLICT(name) DO UPDATE SET phonenumber=excluded.phonenumber ``` Here's the problem: the `sqlite-utils` `.upsert()` and `.upsert_all()` methods don't do this. They use the following SQL: ```sql INSERT OR REPLACE INTO [{table}] ({columns}) VALUES {rows}; ``` If the record already exists, it will be entirely replaced by a new record - as opposed to updating any specified fields but leaving existing fields as they are (the behaviour of "upsert" in SQLite itself). | 140912432 | issue | {"url": "https://api.github.com/repos/simonw/sqlite-utils/issues/66/reactions", "total_count": 1, "+1": 1, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed |