I'm going to make an absolutist statement for which I will probably be corrected shortly. For any given table, interactions should either be done entirely with INSERT or be done entirely with UPSERT. The two should never mix on a single table, and UPDATE should never be used.
My reasoning is that if a table represents the current state of the world, then any state changes should be made with UPSERT in order to bring the table up to date with the world. If a table represents the history of changes, then that history should be appended to with INSERT, but not modified.
I'm sure that there are other cases that I'm not currently considering, but I'm also a newbie at SQL and would love to be told of them.
I'm probably being stupid here but the majority of updates only involve a limited number of columns. How would you translate a statement like 'UPDATE customers SET surname='Smith' WHERE customer_id = 1234' into an UPSERT?
I would argue that your SQL statement should match the information that a user put in. It would be pretty rare for a user to directly input a single field change and their id. More likely, there would be some "personal information" submission, where all fields are available to edit. I would have a separate "customer_personal_info" table that gets UPSERTed with the entire contents on the submission.
One impact of this is that is forces the changes to be atomic. If an object-oriented interface updates a database whenever properties are changed, then it results in many single-column changes. In the example below, if there is a network failure between the two commands, then changing the name from "Jane Doe" to "John Smith" could result in an unintended intermediate state of "John Doe".
>I would argue that your SQL statement should match the information that a user put in.
I'm skeptical this is desirable even just from a security perspective.
I think you're considering the role of the database in a narrower context than they're used. Certainly the databases I work with, it's a minority of updates (actually a very small minority) that are performed as a result of a user inputting something into a form in the sense you're talking (i.e. updating an entity). Think of how many updates are of the form update this order to change status to shipped or product stock available to X. You wouldn't want to be obligated to pass the entire row of something to an application so it could pass back a single changed column.
This is not to even get into the scenario where an update is applied to a view (which might be a limited result from one or more tables). In that sort of scenario it's not even clear what an UPSERT would even do.
My reasoning is that if a table represents the current state of the world, then any state changes should be made with UPSERT in order to bring the table up to date with the world. If a table represents the history of changes, then that history should be appended to with INSERT, but not modified.
I'm sure that there are other cases that I'm not currently considering, but I'm also a newbie at SQL and would love to be told of them.