MySQL (and subsequently SQLite) also support the REPLACE INTO syntax: REPLACE INTO my_table (pk_id, col1) VALUES (5, ‘123’); This automatically identifies the primary key and finds a matching row to update, inserting a new one if none is found. Documentation: https://dev.mysql.com/doc/refman/8.0/en/replace.html
Field should appear either in $set, or in $setOnInsert. Not in both.
Your assumption is right, this is the optimal way to do it and it’s called upsert/merge. Importance of UPSERT – from sqlservercentral.com: For every update in the case mentioned above we are removing one additional read from the table if we use the UPSERT instead of EXISTS. Unfortunately for an Insert, both the UPSERT and … Read more
INSERT OR IGNORE INTO visits VALUES ($ip, 0); UPDATE visits SET hits = hits + 1 WHERE ip LIKE $ip; This requires the “ip” column to have a UNIQUE (or PRIMARY KEY) constraint. EDIT: Another great solution: https://stackoverflow.com/a/4330694/89771.
SQLAlchemy does have a “save-or-update” behavior, which in recent versions has been built into session.add, but previously was the separate session.saveorupdate call. This is not an “upsert” but it may be good enough for your needs. It is good that you are asking about a class with multiple unique keys; I believe this is precisely … Read more
Q&A Style Well, after researching and fighting with the problem for hours, I found out that there are two ways to accomplish this, depending on the structure of your table and if you have foreign keys restrictions activated to maintain integrity. I’d like to share this in a clean format to save some time to … Read more
ON CONFLICT requires a unique index* to do the conflict detection. So you just need to create a unique index on both columns: t=# create table t (id integer, a text, b text); CREATE TABLE t=# create unique index idx_t_id_a on t (id, a); CREATE INDEX t=# insert into t values (1, ‘a’, ‘foo’); INSERT … Read more
Use INSERT … ON DUPLICATE KEY UPDATE. For example: INSERT INTO `usage` (`thing_id`, `times_used`, `first_time_used`) VALUES (4815162342, 1, NOW()) ON DUPLICATE KEY UPDATE `times_used` = `times_used` + 1
Postgres hasn’t implemented an equivalent to INSERT OR REPLACE. From the ON CONFLICT docs (emphasis mine): It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. Though it doesn’t give you shorthand for replacement, ON CONFLICT DO UPDATE … Read more
The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important. For all other cases, though, do not update identical rows without need. Even if … Read more