Content Comparison

merge into key_seq_generator d 
using (
  select
    p.identifier as parent_id, count(c.identifier) as children
  from 
    catissue_specimen p 
    inner join catissue_specimen c on c.parent_specimen_id = p.identifier 
  where 
    c.label is not null
  group by
    p.identifier
) s on (d.key_type = 'PSPEC_UID' AND d.key_value = to_char(s.parent_id))
when matched then 
  update set d.key_sequence_id = to_char(s.children)
when not matched then 
  insert 
    (identifier, key_type, key_value, key_sequence_id) 
  values
    (key_generator_seq.nextval, 'PSPEC_UID', to_char(s.parent_id), to_char(s.children))