If the insert operation would cause the conflict target uniqueness constraint to fail, then the insert is omitted and the corresponding DO NOTHING or DO UPDATE operation is performed instead.
The ON CONFLICT clauses are checked in the order specified. If the last ON CONFLICT clause omits the conflict target, then it will fire if any uniqueness constraint fails which is not captured by prior ON CONFLICT clauses.

This is a tweak coming from PostgreSQL. This RDBMS has it natively implemented in other SQL engines the user has to create on their own.
So the PostgreSQL users are lucky and the others have a little bit more work to do.

Easy way how to do it is this:

IF EXISTS (SELECT 1 FROM table WHERE primary_key = @pkey)
BEGIN
  UPDATE table SET val1 = @val1 ... valN = @valN WHERE primary_key = @pkey;
END
ELSE
BEGIN
  INSERT table(primary_key, val1 ... valN) VALUES(@pkey, @val1 ... @valN); 
END

Example from PostgreSQL

CREATE TABLE vocabulary(word TEXT PRIMARY KEY, count INT DEFAULT 1);
INSERT INTO vocabulary(word) VALUES('jovial')
  ON CONFLICT(word) DO UPDATE SET count=count+1;
Tags: ,