update value only when value changed

Question

I have to update main_table data from another table. Below is statement

We will have value in any one column among value_string,value_date in a row or for a key, based on type_key. (like if type_key is string then value_string will have value and value_date is null). There is a trigger which ensure this constraint.

update main_table t set
    value_string=value_string,
    value_date=value_date,
    value_int=value_int,
    updated_on=now(),
    status=status
from import_table imp where t.key=imp.key

even if there was no change in values of value_string or value_date, then updated_on will change. I want updated_on to be updated only if there is change in values. so i'm changing update query to below

update main_table t set
    value_string=value_string,
    value_date=value_date,
    updated_on= (case when type_key='string' and t.value_string!=imp.value_string 
                    then now()
                 when type_key='date' and t.value_date!=imp.value_date 
                    then now()
                 when type_key='int' and t.value_int!=imp.value_int
                    then now()
                else updated_on end),
    status=status
from import_table imp where t.key=imp.key

Is there a better approach to rewrite above query to improve the performance of query ?

Answer 1

I would add a WHERE condition that only changes the row if at least one of the values is different.

update main_table t 
 set
    value_string = imp.value_string,
    value_date = imp.value_date,
    value_int = imp.value_int,
    updated_on = now(),
    status = imp.status
from import_table imp 
where t.key = imp.key
  and (   t.value_string is distinct from imp.value_string
       or t.value_date is distinct from imp.value_date
       or t.value_int is distinct from imp.value_int
       or t.status is distinct from imp.status);

alternatively you can write it as

where t.key = imp.key
  and (t.value_string, t.value_date, t.value_int, t.status) 
      is distinct from (imp.value_string, imp.value_date, imp.value_int, imp.status);