Leave Update Query Value Unchanged with ADOQuery and Delphi 10.2

I have a Interface between 2 systems that exchange data between each other. This is a default interface that could be modified by GUI, for example you can choose between using a group of fields on both sides, or you can choose to leave the value as is.

Now in my code there is the UPDATE SQL query that looks like that

UPDATE TABLE1 SET
FIELD1_GROUP1=:FIELD1_GROUP1,FIELD2_GROUP1=:FIELD2_GROUP1,FIELD3_GROUP1=:FIELD3_GROUP1,
FIELD1_GROUP2=:FIELD1_GROUP2,FIELD2_GROUP2=:FIELD2_GROUP2,FIELD3_GROUP2=:FIELD3_GROUP2

ADOQuery.Parameters.ParamByName('FIELD1_GROUP1').Value := MyField1_Group1_From_SystemB;
....

Now if I choose to not use GROUP2, it is not Possible to leave the Parameter unset. I have found a solution that works with something like

if USE_GROUP2 then
begin
ADOQuery.SQL.add(FIELD1_GROUP2=:FIELD1_GROUP2,FIELD2_GROUP2=:FIELD2_GROUP2,FIELD3_GROUP2=:FIELD3
_GROUP2);
  ADOQuery.Parameters.ParamByName('FIELD1_GROUP1').Value := MyField1_Group1_From_SystemB;
  ...
end;

But that kind of solution would get between my system to see the fully SQL QUERY in the beginning of every function that uses SQL. It would break our clean code into badly readable Blocks.

I Wonder if parameter has no Function to remove Parameter within its Value. Thanks for Help

Solution

You could use a coalesce in your query to update the field to the value it already had:

UPDATE TABLE1 SET
  FIELD1_GROUP1=COALESCE(:FIELD1_GROUP1, FIELD1_GROUP1) 
  ...

But this has some potential issues.

First of all, quite clear, this won't allow you anymore to update a field to NULL. Of course you can solve that by not using COALESCE, but a CASE which checks for an extra parameter which indicates whether or not group 2 should be updated.

Secondly, and harder to determine the consequences of, depending on the database you're using this may or may not affect how triggers are executed and/or how a rowcount for the update statement is calculated. This might not be an issue right away, but you may see how this could lead to a whole class of hard to debug issues.

So while it can be a solution, I would recommend to write a statement that updates exactly and only the fields that you need to update. This means to have either two statements (one that includes group2 and one that doesn't), or build the SQL dynamically depending on what you need to update.

I also like to have the exact SQL, so my preference would be to have two explicit statements, but depending on the situation I might go for the other solution. That decision is up to you.