A common scenario is to try and insert a record which, if it matches an existing record (i.e., has the same PK value), becomes an update to that record.
ANSI SQL introduced the MERGE statement for merging a source table into a target table that supports options for handling conflicts (update or delete). We could extend that syntax to support "contextually typed table value constructor" (i.e., VALUES) but this would be non-standard, and perhaps overkill in the flexibility it provides (the ability to specify an arbitrary update or delete specification on match (with an optional additional filter condition) or arbitrary insert statement on not matched (with an optional additional filter condition).
MySQL has an ON DUPLICATE KEY UPDATE option for INSERT, while SQLLite has an ON CONFLICT REPLACE.
A key issue (pardon the pun) is how to define conflicts. Does the client specify a unique key field, or does the implementation pick one?
Also consider ability to use in a bulk operation.
There is a good discussion of the different approaches here.
A common scenario is to try and insert a record which, if it matches an existing record (i.e., has the same PK value), becomes an update to that record.
ANSI SQL introduced the MERGE statement for merging a source table into a target table that supports options for handling conflicts (update or delete). We could extend that syntax to support "contextually typed table value constructor" (i.e., VALUES) but this would be non-standard, and perhaps overkill in the flexibility it provides (the ability to specify an arbitrary update or delete specification on match (with an optional additional filter condition) or arbitrary insert statement on not matched (with an optional additional filter condition).
MySQL has an ON DUPLICATE KEY UPDATE option for INSERT, while SQLLite has an ON CONFLICT REPLACE.
A key issue (pardon the pun) is how to define conflicts. Does the client specify a unique key field, or does the implementation pick one?
Also consider ability to use in a bulk operation.
There is a good discussion of the different approaches here.