1

I have table adv_days(adv_date date not null primary key, edit_state integer) and I want to do series of commands: check wether '27.11.2019' exists in table, if exists then update edit_state to 1, if does not exist, then insert date '27.11.2019' with edit_state=1.

I have come up with Firebrid merge statement:

merge into adv_days d
  using (select adv_date, edit_state from adv_days where adv_date='27.11.2019') d2
  on (d.adv_date=d2.adv_date)
when matched then update set d.edit_state=1
when not matched then
  insert(adv_date, edit_state)
  values(:adv_date, 1);

It compiles and works as expected, but all the examples that I have read for merge is about application to 2 tables. So - maybe there is better solution for select-check-update operatios for one table than merge?

This is design question, but so concrete that SO is more appropriate than software engineering.

TomR
  • 2,696
  • 6
  • 34
  • 87
  • 1
    It's a perfectly valid use case for a `MERGE` statement. Firebird also supports `update or insert` https://firebirdsql.org/refdocs/langrefupd25-update-or-insert.html –  Nov 27 '19 at 08:17
  • I didn't know about update-or-insert, of course, I will use it now instead of merge or individual statements. – TomR Nov 27 '19 at 08:22
  • 1
    @a_horse_with_no_name Consider posting your comment as an answer. – Mark Rotteveel Nov 27 '19 at 10:08

1 Answers1

1

There is community response, that merge for insert/update for one table is perfectly valid solution. But, of course, update or insert is more elegant solution, e.g. in this case the code is:

update or insert into adv_days (adv_date, edit_state)
  values ('27.11.2019', 1)
  matching (adv_date)

This solution has increased performance over merge (as mentioned in the question) as one can observe from the plan and the number of indexed reads in the case when both statements are repeated serveral times.

There is drawback in both cases still: repeated execution results in update although it would not be necessary to touch the record which already has all the required values.

TomR
  • 2,696
  • 6
  • 34
  • 87
  • 1
    The drawback can be addressed in the case of `merge` by excluding records with `edit_state = 1` from the select in the `using` clause, or - in Firebird 3 - using a conditional match like `when matched and d.edit_state <> 1` – Mark Rotteveel Nov 28 '19 at 10:56