-
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
-
9i? Look at the merge command.
-
-
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.
-
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 ?
-
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
-
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 02:11 PM.
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|