0

I'm running a merge statement but I want the update to happen only if there is exactly one row matching.

merge into mergetest using dual on (a = 1)
  when matched then update set b = b+1;

I know I can include a where clause at the end, but I have no idea what to do. Group functions are not allowed, so count(*) = 1 doesn't work.

Roland
  • 7,525
  • 13
  • 61
  • 124
  • Unless you introduce some form of serialization (e.g. locking), there's nothing stopping another session inserting a row *after* your code starts - and this is true whether you use a merge, or a select + update. – Jeffrey Kemp Feb 11 '15 at 08:18

2 Answers2

1

Does this work?

merge into mergetest
    using (select a, count(*) as cnt
           from mergetest
           where a = 1
           group by a
          ) mt
          on mergetest.a = mt.a and cnt = 1
   when matched then
        update set b = b + 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This looks complicated. I'm actually running the query in plsql and an easy way would be to check for matches first and then do an `if-then-else`. I would only do it in plain SQL if there is an elegant solution. – Roland Feb 07 '15 at 13:23
  • 1
    @Roland . . . Given what you want to do, a subquery that counts the rows for a given value of `a` doesn't seem inelegant. It seems necessary, whether done in a `merge` or a PL/SQL block. – Gordon Linoff Feb 07 '15 at 13:29
  • See my answer on how to do it with PL/SQL: http://stackoverflow.com/a/28383194/480894 – Roland Feb 07 '15 at 14:19
  • @Roland . . . I'm pretty sure that in a `merge` statement, the `using` clause is evaluated only once. However, you would need to look at the execution plan to guarantee it. – Gordon Linoff Feb 07 '15 at 16:39
  • There is one problem with your solution. What if you want to add a not matched clause to insert in case there are 0 matching rows? How do you differentiate this case from the case of more than one matching row? – Roland Feb 11 '15 at 11:18
  • Also consider that the select in the using clause will not return anything if there is no match. In this case even an existing `when not matched` clause is not executed. – Roland Feb 11 '15 at 15:57
0

In PL/SQL this could be solved in the following way:

select count(*) into l_count from mergetest  where a = 1;

case l_count
  when 0 then
    insert into mergetest  (a,b) values (1,1);
  when 1 then
    update  mergetest  set b = b + 1 where a = 1;
  else
    NULL;
  end case;
Roland
  • 7,525
  • 13
  • 61
  • 124