Firebird update or insert in 'with a link to the field in the values?

Question

I would like to update or insert a row in a Firebird 2.5 table, but I want the values ​​that I insert to be ignored if they are null.

I was hoping it would be like this:

update or insert into TargetTable ( FieldA, FieldB ) values ( :fielda, coalesce(:fieldb, FieldB )) 

However, Firebird does not allow referencing FieldB in the "values" list.

This enables the update syntax:

 update TargetTable set FieldB = coalesce( :fieldb, FieldB ) where FieldA = :fielda 

But this requires that I handle the inserts separately.

Is there a way to get both an update / insert and a union into a field value?

Answer 1

Have a look here: http://tracker.firebirdsql.org/browse/CORE-3456

You can look in the MERGE instruction: http://www.firebirdsql.org/refdocs/langrefupd21-merge.html

 merge into TargetTable e using (select :fielda FieldA, :fieldb FieldB from RDB$DATABASE ) n on e.FieldA = n.FieldA when matched then update set e.FieldB = coalesce( n.FieldB, e.FieldB ) when not matched then insert (FieldA, FieldB) values ( n.FieldA, n.FieldB )