Whether the fact that you can't reset the columns to NULL means you can't use this solution or not depends on the situation. What if all the columns in question disallow NULLs? I would not consider this a general reason to frown upon this solution. It's just something you have to keep in mind. How often do you actually want to set something to NULL?

I do not understand the second reason. Sounds like you thought that building dynamic SQL including the values would be the best solution then. And I'm actually not sure what log file do you mean. Could you explain?

Without benchmarking I would not dare to say whether the additional burden of "updating" some fields by the old values is bigger that the burden of either recompiling the SQL each time or keeping an exponential number of prepared statements. And I bet the results would differ a lot depending on the indexes and triggers on the table and the size of those columns.

Sure it's not dificult (in Perl) to create a query containing just the columns you want to update. The problem is that this way you either force the SQL server to parse and compile the statement each time or you have to cache a potentialy big number of different statements, one for each combination of updated columns. Both are straining the DBMS and the second one heavily complicates the code.

And there is actually one more reason why this IS quite often the one good solution is that quite often you want to keep all SQL inside stored procedures. That way you may rename and/or split tables without having to search for SQL in all programs and pages that might access the database. Plus the DBAs can see/modify/optimize all SQL that's gonna get executed against the database. Neither Monster.com nor VIPeR actually allow any inline SQL, all database access has to go via stored procedures.

And neither constructing and executing some ad-hoc SQL inside the stored procedures nor having an explosion of IFs seem like a very good solution to me. (The first would not be accepted by the Monster DBAs anyway.)

Edit by castaway: Closed small tag in signature