Assuming three columns in the table: ID, NAME, ROLE BAD: This will insert or replace all columns with new values for ID=1: INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, ‘John Foo’, ‘CEO’); BAD: This will insert or replace 2 of the columns… the NAME column will be set to NULL or the … Read more
The error you get: ON CONFLICT DO UPDATE command cannot affect row a second time … indicates you are trying to upsert the same row more than once in a single command. In other words: you have dupes on (name, url, email) in your VALUES list. Fold duplicates (if that’s an option) and the error … Read more
MERGE is atomic meaning that either all changes are committed or all changes are rolled back. It does not prevent duplicate keys in case of high concurrency. Adding holdlock hint will take care of that. MERGE INTO CustomerSpend WITH (HOLDLOCK) AS T USING ( SELECT ? AS ID, ? AS NetValue, ? AS VoidValue ) … Read more
Consider INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=c+1; If a and b are UNIQUE fields, UPDATE occurs on a = 1 OR b = 2. Also when condition a = 1 OR b = 2 is met by two or more entries, update is done only once. Ex here table … Read more
It’s the recurring problem of SELECT or INSERT, related to (but different from) an UPSERT. The new UPSERT functionality in Postgres 9.5 is still instrumental. WITH ins AS ( INSERT INTO names(name) VALUES (‘bob’) ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE SET name = NULL WHERE FALSE — never executed, but locks the row RETURNING … Read more
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
Postgres 9.5 implemented UPSERT. See below. Postgres 9.4 or older This is a tricky problem. You are running into this restriction (per documentation): In a VALUES list appearing at the top level of an INSERT, an expression can be replaced by DEFAULT to indicate that the destination column’s default value should be inserted. DEFAULT cannot … Read more
Basically, DB2 doesn’t know what data types you’re sending in on those parameters. I’m guessing you’re either on an older version of DB2 (less than 9.7 on Linux/Unix/Windows, or on a Mainframe version older than 10.1), which doesn’t do a whole lot of “automatic” type conversion. Or you’re sending in NULL values (which still have … Read more
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
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