How to re-factor bulk update with nested query

Question

I was wondering how can I re-factor this script since both nested queries are quite similar (I'm using ORACLE for my database):

  UPDATE
  ALERT alert
  SET
    VERIFICATION_COUNT =
      (
        SELECT COUNT(*)
        FROM TRANSCRIPTION trans
        INNER JOIN DOS dos
        ON trans.REF_NO = dos.REF_NO
        WHERE trans.STATUS = 'ONGOING'
        AND alert.VISIT = dos.VISIT
      )
  WHERE
      alert.VISIT = 
      (
        SELECT DISTINCT dos.VISIT FROM TRANSCRIPTION trans
        INNER JOIN DOS dos
        ON trans.REF_NO = dos.REF_NO
        WHERE trans.STATUS = 'ONGOING'
        AND alert.VISIT = dos.VISIT
      );

score 0 · Accepted Answer · answered Jun 04 '14 at 11:14

Try this:

merge into alert A
using (
    select D.visit, count(*) as visit_count
    from transcription T
        join dos D on D.ref_no = T.ref_no
    where T.status = 'ONGOING'
    group by D.visit
) X
on ( A.visit = X.visit )
when matched then
    update
    set A.verification_count = X.visit_count
;

I don't guarantee, though, that it does exactly what the original does. :-) That's because without knowing foreign key relationships I'm a little confused as to what the original WHERE clause does since an equality operator is used against a result set, not against a scalar subquery.

Wow it worked! Thanks! I only knew of oracle's merge function just now. — dilm, Jun 05 '14 at 01:17

How to re-factor bulk update with nested query

1 Answers1