SQL Question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: SQL Question

  1. #1
    Join Date
    Jul 2000
    Posts
    23

    Post SQL Question

    Hello ...

    I need some help ...

    I need to update the records in the master table from the temp table if the master record is present & insert the new record if record doesn't exist in the master table.

    I should perform this as one time batch process ..

    any idea ?

    thanks

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    9i? Look at the merge command.

  3. #3
    Join Date
    Jul 2000
    Posts
    23
    No ..I'm on Oracle 8.0

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    You'll be wanting to use PL/SQL, cursors, and loops. Cursor for each table. Loop thru each cursor comparing values/existence/whatever it is that makes you decide to insert/update from the temp table. All of this done in a PL/SQL prodedure. Piece of cake.

  5. #5
    Join Date
    Jul 2000
    Posts
    23
    Every row in the temp table should be checked against 1 million records ?
    if the TEMP table has 1 million records ..do you think PL/SQL Cursors would be the efficient way ?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You definitely don't want to do cursors.

    You want to do:
    Code:
    UPDATE
       MainTable   MT
    SET
       (
       Col2   ,
       Col3   ,
       Col4
       )   =   (
             SELECT
                Col2   ,
                Col3   ,
                Col4
             FROM
                TempTable   TT
             WHERE
                TT.Col1   =   MT.Col1
             )
    WHERE
       EXISTS
          (
          SELECT
             1
          FROM
             TempTable   TT
             WHERE
                TT.Col1   =   MT.Col1
          )
    followed by
    Code:
    INSERT INTO 
       MainTable
          (
          Col1   ,
          Col2   ,
          Col3   ,
          Col4
          )
    SELECT
       Col1   ,
       Col2   ,
       Col3   ,
       Col4
    FROM
       TempTable TT
    WHERE
       NOT EXISTS
          (
          SELECT
             1
          FROM
             MainTable   MT
          WHERE
             MT.Col1   =   TT.Col1
          )
    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Three issues.
    1) You could provide more information about the size and scope of things you want to have done when you first post your question. Are you asking about a million records because you don't believe it can be done, or because you think I am stupid for suggesting the PL/SQL approach? That's just one way to do things.
    2) Setting the methodology aside for the moment, based on your problem statement (checking records in a temp table against a master table, then inserting or updating depending on the case), does it not strike you as being obvious that you will be doing a record-by-record comparison in some form or another? The insert case is simple. Not there, insert it. What is your update criteria? How else will you know what to do with a record in the temp table?
    3) Is PL/SQL the best way for your application? Who knows? Maybe you should consider COBOL or some other procedural call to do the work. You can look in just about any programming logic book and see how this problem is done - on a daily basis by a gazillion companies dealing with a bazillion records. Banking/updating customer accounts is a typical example of comparing a record in a temp table to a master table.
    Last edited by stecal; 04-18-2003 at 03:11 PM.

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I see the crux of that rant as:

    Originally posted by stecal
    Three issues.
    2) Setting the methodology aside for the moment, based on your problem statement (checking records in a temp table against a master table, then inserting or updating depending on the case), does it not strike you as being obvious that you will be doing a record-by-record comparison in some form or another?
    My reply would be yeah, but *how* that comparison is done is of great consequence. SQL is almost always the best way to go. If it can't be done in SQL, then PL/SQL. If it can't be done in PL/SQL, then Java or something else *on the server*. Finally, the worst way would be in some other language across the network.

    The rest of the rant (not that rants are bad - I do them all the time ) falls in behind the same argument. Yes, you could open any programming book and see how to do it inefficiently in any language. The SQL solution I provided is most likely faster than any other language.

    But basically, I'm not sure what set off this rant. The original question was valid, the response was reasonable, and the question about efficiency seemed genuine and was, as it turns out, dead on. Looping through a million records and testing each one seperately inside a PL/SQL cursor would indeed be slow. Nobody thinks you're stupid, or at least I don't think anybody insinutated that.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    I didn't mean for it to sound like a rant. I feel like I walked into one of those seemingly easy questions to answer and was met with one of those "oh, by the way, this has to work for a million records, you must be kidding about your answer" replies. For his million records, as it turns out, there could be anywhere from one million to two million SQL queries (you test for inserts first and the temp records are all new = one million, but if they are all updates, you're doing two queries, so there are some design issues to consider). Using a PK, the inserts are easy. The updates could be more complex because of what the update condition is, i.e., what's being tested, but why bother testing? If the PK is in the temp table, then do a blanket update of that record in the master table. Either way, SQL or other language, there are a lot queries. Do you want to do them one at a time or group them (cursor)? I agree that for a large number of records that cursors are probably not the best approach

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by stecal
    You'll be wanting to use PL/SQL, cursors, and loops. Cursor for each table. Loop thru each cursor comparing values/existence/whatever it is that makes you decide to insert/update from the temp table. All of this done in a PL/SQL prodedure. Piece of cake.
    You can't be serious when you suggest using "Cursor for each table. Loop thru each cursor comparing...". Why would you need more than one cursor? What use would it be of a cursor on a *master table*? And you don't need to perform any comparison, Oracle will do it for you (unless original poster will come back explaining his master table has no primary key).
    Code:
    BEGIN
      FOR r IN (SELECT * FROM temp_table) LOOP
        BEGIN
          INSERT INTO master_table (colA, colB, ....)
          VALUES (r.col1, r.col2, ....);
        EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
          UPDATE master_table SET colB = r.col2, .....
          WHERE colA = r.col1;
        END;
      END;
    END;
    But as Chris said, pure SQL approach should be inspected first to see if it's feasible.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width