Ariel Sakin wrote:
I have the following prepared statement:
UPDATE TABLE requests SET f1=?, f2=?, f3=?
Where f1 f2 and f3 are columns that may contain large string values (some of them may be BLOBs). In reality I have 20+ columns.
I am looking for a way of using that statement for updating my table without having to provide it with all the values all the time. for example, if I need to update only f1 and f3 I don't want to be forced to pass the value for f2 that do not need to be changed. Writing a different statement for every column is not a good option since sometimes I need to update 15 out of 20 fields and the overhead of running 15 queries is too large.
Is there a way to do it?
Roger Vellacott answers:
You can write the update query in the following form:
UPDATE MY_TABLE SET MYFIELD1 = COALESCE(:MYPARAM1,MYFIELD1), MYFIELD2 = COALESCE(:MYPARAM2,MYFIELD2) WHERE ...etc
Make sure that the parameter values are NULL for those fields you do not want to update.