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
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
INSERT INTO <table> SELECT <natural keys>, <other stuff…> FROM <table> WHERE NOT EXISTS — race condition risk here? ( SELECT 1 FROM <table> WHERE <natural keys> ) UPDATE … WHERE <natural keys> there is a race condition in the first INSERT. The key may not exists during the inner query SELECT, but does exist at … 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
The pattern is (without error handling): SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN TRANSACTION; UPDATE #TProductSales SET StockQty = @StockQty, ETA1 = @ETA1 WHERE ProductID = @ProductID; IF @@ROWCOUNT = 0 BEGIN INSERT #TProductSales(ProductID, StockQTY, ETA1) VALUES(@ProductID, @StockQTY, @ETA1); END COMMIT TRANSACTION; You don’t need to perform an additional read of the #temp table here. You’re … Read more
Have a look at http://sqlite.org/lang_conflict.html. You want something like: insert or replace into Book (ID, Name, TypeID, Level, Seen) values ((select ID from Book where Name = “SearchName”), “SearchName”, …); Note that any field not in the insert list will be set to NULL if the row already exists in the table. This is why … Read more
No there isn’t really a better solution to this, so perhaps with an explanation. Suppose you have a document in place that has the structure as you show: { “name”: “foo”, “bars”: [{ “name”: “qux”, “somefield”: 1 }] } If you do an update like this db.foo.update( { “name”: “foo”, “bars.name”: “qux” }, { “$set”: … 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.
Postgres 9.5 (released since 2016-01-07) offers an “upsert” command, also known as an ON CONFLICT clause to INSERT: INSERT … ON CONFLICT DO NOTHING/UPDATE It solves many of the subtle problems you can run into when using concurrent operation, which some other answers propose.
As mentioned in my comment, you don’t have to do the subselect to reference to the row that’s causing ON DUPLICATE KEY to fire. So, in your example you can use the following: INSERT INTO `item` (`item_name`, items_in_stock) VALUES( ‘A’, 27) ON DUPLICATE KEY UPDATE `new_items_count` = `new_items_count` + 27 Remember that most things are … Read more