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!

Is there a better way of doing this?

Discussion in 'SQL PL/SQL' started by awdigrigoli, Feb 22, 2011.

  1. awdigrigoli

    awdigrigoli Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    update Table a
    set
    a.col1 = (select col1 from TableCol1 where (column) = (a.column)),
    a.col2 = (select col2 from TableCol2 where (column) = (a.column)),
    a.col3 = (select col3 from TableCol3 where (column) = (a.column)),
    a.col4 = (select col4 from TableCol4 where (column) = (a.column));

    I have a few tables filled that will each be used to supply the main table with data based on matching columns between the main and each of the helper tables.

    Would a JOIN be faster? I am not sure how to make that work just yet.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It might be faster, it might not. Your update would look like this:

    update Table a
    set (col1,col2,col3,col4) = (select t1.col1, t2.col2, t3.col3, t4.col4 from TableCol1 t1 join TableCol2 t2 on (t1.column = t2.column) join TableCol3 on (t1.column = t3.column) join TableCol4 t4 on (t1.column = t4.column) where t1.column = a.column);

    however it may not be an improvement. You'll need to check the execution plans for the two update statements and choose the better of the two.
     
  3. awdigrigoli

    awdigrigoli Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    thanks David. I'll give it a shot. Still a little ways away from this portion of the project.