Search code examples
sqlfirebird

Avoid unnecessary update in "update or insert" SQL?

Here is Firebird SQL description for update or insert:

https://firebirdsql.org/refdocs/langrefupd25-update-or-insert.html

And my example

update or insert into books (id, name)
  values (555, 'Good book')
  matching (id)

If book already exists with id=555 and name='Good book' then this command executes update statement anyway and the triggering of update triggers is the only effect of such update, no change of the values for the record. Is it possible to request in this SQL that there is not need to execute update, if the updatable fields (e.g. name in this case) have the specified values already.

My question is for Firebird 2, 3, 4. But if there is similar preventing clause in SQL server, MySQL or Oracle, then I would be happy to hear.

Solution

  • If you don't want the update, then you should not use UPDATE OR INSERT, but instead use MERGE, or use INSERT and catch and ignore the duplicate key error (assuming you have a suitable primary or unique key constraint).

    Example of using MERGE:

    merge into books 
using (
  select 555 as id, 'Good book' as name
  from rdb$database) as src
on books.id = src.id
when not matched then
  insert (id, name) values (src.id, src.name)