4 ms·

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

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?

MereInterest 6y ago
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". customer_obj.set_forename("John") customer_obj.set_surname("Smith") (Again, I am by no means an expert, and in part posted this so that I can be corrected if/where I am wrong.)
- DharmaPolice 6y ago
  >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.

MereInterest 6y ago

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". customer_obj.set_forename("John") customer_obj.set_surname("Smith") (Again, I am by no means an expert, and in part posted this so that I can be corrected if/where I am wrong.)

DharmaPolice 6y ago
>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.