CODEWITHSUNDEEP
sqloracle-database
Roland
Roland

Reputation: 7853

Merge statement but update only if exactly *one* row matches

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.

Upvotes: 0

Views: 1759

Answers (2)

Roland
Roland

Reputation: 7853

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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;

Upvotes: 1

Related Questions