#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0

    Need to re-write a MSSQL update statement to work in Oracle


    How do I write this MSSQL statement so it works in Oracle?

    update b1
    set b1.b1_app_status = r3.application_status
    from conv_app_status_update a, statyp r3, b1perm b1
    where a.spc = r3.serv_code
    and a.task_des = r3.r3_act_type_des
    and a.task_status =r3.r3_act_stat_des
    and a.process_code = r3.r3_process_code
    and r3.application_status is not null
    and a.spc = b1.serv_code
    and a.id1 = b1.id1
    and a.id2 = b1.id2
    and a.id3 = b1.id3
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    840
    Rep Power
    387

    Cool


    Perhaps this?:
    Code:
    UPDATE b1perm b1
       SET b1_app_status      =
          (SELECT r3.application_status
           FROM conv_app_status_update a, statyp r3
           WHERE a.spc = r3.serv_code
           AND   a.task_des = r3.r3_act_type_des
           AND   a.task_status = r3.r3_act_stat_des
           AND   a.process_code = r3.r3_process_code
           AND   r3.application_status IS NOT NULL
           AND   a.spc = b1.serv_code
           AND   a.id1 = b1.id1
           AND   a.id2 = b1.id2
           AND   a.id3 = b1.id3 )
     WHERE EXISTS
            (SELECT '?'
             FROM conv_app_status_update a, statyp r3
             WHERE a.spc = r3.serv_code
             AND   a.task_des = r3.r3_act_type_des
             AND   a.task_status = r3.r3_act_stat_des
             AND   a.process_code = r3.r3_process_code
             AND   r3.application_status IS NOT NULL
             AND   a.spc = b1.serv_code
             AND   a.id1 = b1.id1
             AND   a.id2 = b1.id2
             AND   a.id3 = b1.id3);

IMN logo majestic logo threadwatch logo seochat tools logo