The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the “DO NOTHING” ought to mean “don’t increment anything”. You’re picturing this: Check values to insert against constraint If duplicate detected, abort Increment sequence Insert data But in fact, … 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
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
I use AddOrUpdate in this situation. However, I believe it does query the database first in order to decide to issue an insert or update. context.MyEntities.AddOrUpdate(e => e.Id, entity); Update: I ran through my debug log files. First it runs: SELECT TOP (2) … WHERE 1 = [Extent1].[Id] Then it runs either: INSERT [dbo].[TestTable](…) VALUES … Read more
I think that this is an interesting question that deserves an in-depth answer; please bear with me if it is a little bit lengthy. In short: Your guess is right, and you can use the following RETURNING clause to determine if the row was inserted and not updated: RETURNING (xmax = 0) AS inserted Now … Read more
Modern releases of pymongo ( greater than 3.x ) wrap bulk operations in a consistent interface that downgrades where the server release does not support bulk operations. This is now consistent in MongoDB officially supported drivers. So the preferred method for coding is to use bulk_write() instead, where you use an UpdateOne other other appropriate … Read more
Turns out a special table named excluded contains the row-to-be-inserted (strange name though) insert into USERS( id, username, profile_picture) select unnest(array[‘12345’]), unnest(array[‘Peter’]), unnest(array[‘someURL’]) on conflict (id) do update set username = excluded.username, profile_picture = excluded.profile_picture; http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table’s … Read more
i finally got the Upsert syntax using MERGE in SQL Server 2008. Using what Jacob wanted to do (an Upsert): IF EXISTS(SELECT * FROM member_topic WHERE mt_member = 0 AND mt_topic = 110) BEGIN –update existing row UPDATE member_topic SET mt_notes=”test” WHERE mt_member = 0 AND mt_topic = 110 END ELSE BEGIN –insert new row … Read more
UPDATE will not do anything if the row does not exist. Where as the INSERT OR REPLACE would insert if the row does not exist, or replace the values if it does.
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