1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with using "merge"

Discussion in 'SQL PL/SQL' started by 13478, Apr 4, 2017.

  1. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello Krasnoslobodtsev or someone,

    I have below two "update" sql, it is slow, could you please help to change it to use "merge"

    1.

    Update Mf_Blns T2 Set T2.Alc=(Select unique Substr(T1.Alc_Id,Instr(T1.Alc_Id,'&',1,2)+1,Length(T1.Alc_Id)- Instr(T1.Alc_Id,'&',1,2)+1)
    From Mf_Money T1
    Where T2.Fund= T1.Nm
    And (T2.Ebfy=T1.Ebfy or (t2.ebfy is null and t1.ebfy is null))
    And T2.Bbfy=T1.Bbfy
    And T2.Patn=Substr(T1.Patn_Id,Instr(T1.Patn_Id,'&',1,2)+1,Length(T1.Patn_Id)- Instr(T1.Patn_Id,'&',1,2)+1))
    Where
    t2.alc is null;



    2.

    Update Mf_Blns Set (Mf_Blns.S224_Rcsf_Fl) = (Select Atable.S224_Rcsf_Fl From MF_AV_PN Atable Where Mf_Blns.Line_Id = Atable.Uidy) Where Exists
    (Select 1 From MF_AV_PN Atable Where Mf_Blns.Line_Id = Atable.Uidy);

    Thank you very much !!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,619
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Making those statements MERGE statements isn't likely to improve their performance as you add a layer of complexity; additionally some versions of Oracle contain a bug in the MERGE command so you might want to read here:

    https://dfitzjarrell.wordpress.com/2015/02/23/merge-right/

    In the absence of the bug a MERGE statement is both an insert and update statement (and in later releases it can also process deletes) so it's possibly using more resources than your basic update would consume. In addition you need to have valid statements for the WHEN MATCHED and WHEN NOT MATCHED sections or the MERGE will fail to execute. I see nothing in those statements that would allow for the INSERT portion to be coded properly, and since you haven't posted any create table statements or provided any sample data it's difficult to help you construct such a MERGE statement.
     
  3. 13478

    13478 Active Member

    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Thank you David

    As to

    1.
    Merge into Mf_Blns T2
    USING (Select unique Substr(T1.Alc_Id,Instr(T1.Alc_Id,'&',1,2)+1,Length(T1.Alc_Id)- Instr(T1.Alc_Id,'&',1,2)+1) alc,Nm,Ebfy From Mf_Money T1) s
    on ((T2.Fund= s.Nm) and (T2.Ebfy=s.Ebfy or (t2.ebfy is null and s.ebfy is null)) and (T2.Patn=Substr(T1.Patn_Id,Instr(T1.Patn_Id,'&',1,2)+1,Length
    (T1.Patn_Id)- Instr(T1.Patn_Id,'&',1,2)+1)) Where t2.alc is null))
    WHEN matched THEN UPDATE SET T2.Alc = s.Alc;

    2.

    Merge into Mf_Blns T2
    USING (Select d. S224_Rcsf_Fl,d.Uidy From MF_AV_PN d, Mf_Blns e where e.Line_ID=d.Uidy) s
    on (T2.Line_Id = s.Uidy)
    WHEN matched THEN UPDATE SET T2.S224_Rcsf_Fl=s.S224_Rcsf_Fl;

    please correct me, if I am wrong.

    thank you very much
     
    Last edited: Apr 5, 2017
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,619
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Those are not complete MERGE statements and will throw syntax errors as the WHEN NOT MATCHED sections are missing. You can't take half of command and execute it.

    Those will not work.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    I thought that you decided this task a long time ago.

    What is primary/unique key on tables :Mf_Blns,Mf_Money, MF_AV_PN .How much rows in the table Mf_Blns with acl is null ? Can you provide description of tables : Mf_Blns,Mf_Money, MF_AV_PN and they statistics here ?