Unanswered: Question about best method to update optional fields.
I think I know the best way for this, but I thought I would throw this up here. Over the last couple of years I have been tasked with developing/supporting the database for our Incident Management Application.
Now I came into this with minimal experience and have been learning all the time, it doesn't help that this isn't my primary role (yet).
When I designed the database it requires an audit trail to pick up changes, to this end the update query reads the record from the database, puts the variables into a set of before variables and then updates the worklog table with before and after details and then does a big update onto the main table.
We have a new app being developed and it is a lot smaller and doesn't need such detailed auditing. Now what method is better.
Make all Input params for fields to update default to null.
If these fields are not null, then make a separate update.
This means no read for the DB and a few update commands (1 to 10ish).
Same method as above. Read in all fields, update any where the param is not null and make one update at the end.
I am leaning toward number 1, but wondered if there was a best practise for this or if thee is an even neater way to do this.