Firebird 'update or insert into' with field reference in values?

Question

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

I was hoping something like this would do it:

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

However, Firebird does not allow the reference to FieldB in the 'values' list.

It does allow this update syntax:

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

But this requires me to handle inserts separately.

Is there a way to get both update/insert and a coalesce on the field value?

tsteinmaurer tsteinmaurer · Accepted Answer · 2013-01-30T22:01:58

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

You might look into the MERGE statement though: 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 )

Firebird 'update or insert into' with field reference in values?

1 Answers