Method for conditionally updating or inserting a row into a table

• US 7,016,903 B1
• Filed: 01/25/2001
• Issued: 03/21/2006
• Est. Priority Date: 01/25/2001
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Ghosh, Bhaskar, Bedi, Harmeek, Thusoo, Ashish
• Primary Examiner(s): Corrielus, Jean M.
• Assistant Examiner(s): To, Baoquoc N.
• Application Number:
  US09/770,358
• Time in Patent Office: 1,881 Days
• Field of Search: 707/2, 707/3, 707/4, 707/103.R, 707/103.Z, 707/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

The present invention provides the ability to alternatively update or insert a row into a table. This functionality is accomplished by merging a source table with a destination table such that the rows in the combined table are classified as an update row or an insert row. An identifier is inserted into each row to indicate its class. Examination of the identifier allows the row to be correctly inserted into the destination table or correctly updated in the destination table.

Claims

1. A computer-implemented method for applying a row from a source table to a destination table, the method comprising:

selecting a first column from a source table;

selecting a second column from a destination table;

performing an outer join operation on the source table and the destination table using the first and second columns, the outer join designating the source table as being preserved;

updating each row in the destination table with a row from the results of the outer join operation containing a matching element in the first and second columns; and

inserting into the destination table each row from the results of the outer join operation with a non-matching element in the first and second columns,

the method performing no more than one scan per table.

2. The method of claim 1 further comprising;

combining the rows in the source table such that the first column has a unique element in each row.

3. The method of claim 2 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

4. The method of claim 1 wherein the outer join operation uses an equal comparison operator for a comparison statement.

5. A statement implementing a computer-implemented process for inserting a new row or updating an existing row in a database table, the process comprising the steps of:

selecting from a source table a first column comprising a plurality of elements;

selecting from a destination table a second column comprising a plurality of elements;

determining a set of matching rows based upon the success of a comparison operation on an element in the first column and an element in the second column;

determining a set of non-matching rows based upon the failure of a comparison operation on the first column element and the second column element;

updating the destination table with the set of matching rows; and

inserting into the destination table the set of non-matching rows,

the statement performing no more than one scan per table.

6. The process of claim 5 further comprising:

combining the rows in the source table, wherein the resulting source table has a unique element in each row of the first column.

7. The process of claim 6 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

8. The process of claim 5 wherein the comparison operation uses an equal comparison operator.

9. A computer-implemented method for upserting a source table with a destination table, the method comprising:

selecting from a source table a first column comprising a plurality of elements;

selecting from a destination table a second column comprising a plurality of elements;

updating a row in the destination table with a row from the source table upon the success of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table; and

inserting a row from the source table into the destination table upon the failure of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table,

the method using no more than one query language statement.

10. The method of claim 9 further comprising:

combining the rows in the source table, wherein the resulting source table has a unique element in each row of the first column.

11. The method of claim 10 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

12. The method of claim 9 wherein the comparison operation uses an equal comparison operator.

13. A computer implemented method for aggregating data in a database, comprising:

parsing from a single command line, a command, a source table, a destination table, a source key, and a destination key;

comparing the source key in each row of the source table with the destination key in each row of the destination table;

determining a set of update rows based upon the success of a comparison operation performed on the source key and the destination key;

determining a set of insert rows based upon the failure of a comparison operation performed on the source key and the destination key;

updating the destination table with the set of update rows; and

inserting into the destination table the set of insert rows;

wherein no more than one command line is parsed.

14. The method of claim 13 further comprising:

combining the rows in the source table, wherein the resulting source table has a unique source key in each row of the source table.

15. The method of claim 14 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the source key;

creating a group of rows, wherein each row in the group of rows contains a matching element in the source key; and

combining the group of rows into a single row.

16. The method of claim 13 wherein the comparison operation uses an equal comparison operator.

17. A computer program product including a medium usable by a processor, the medium having stored thereon a sequence of instructions, wherein when the sequence of instructions is executed by the processor, the processor executes a process for applying a row from a source table to a destination table, the process comprising:

selecting a first column from a source table;

selecting a second column from a destination table;

performing an outer join operation on the source table and the destination table using the first and second columns, the outer join designating the source table as being preserved;

updating each row in the destination table with a row from the results of the outer join operation containing a matching element in the first and second columns; and

inserting into the destination table each row from the results of the outer join operation with a non-matching element in the first and second columns;

the process performing no more than one scan per table.

18. The computer program product of claim 17 wherein the process further comprises combining the rows in the source table such that the first column has a unique element in each row.

19. The computer program product of claim 18 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

20. The computer program product of claim 17 wherein the outer join operation uses an equal comparison operator for a comparison statement.

21. A computer program product including a medium usable by a processor, the medium having stored thereon a sequence of instructions, wherein when the sequence of instructions is executed by the processor, the processor executes a process for inserting a new row or updating an existing row in a database table using no more then one query language statement, the process comprising:

selecting from a source table a first column comprising a plurality of elements;

selecting from a destination table a second column comprising a plurality of elements;

determining a set of matching rows based upon the success of a comparison operation on an element in the first column and an element in the second column;

determining a set of non-matching rows based upon the failure of a comparison operation on the first column element and the second column element;

updating the destination table with the set of matching rows; and

inserting into the destination table the set of non-matching rows,

the no more than one query language statement performing no more than one scan per table.

22. The computer program product of claim 21 wherein the process further comprises combining the rows in the source table, wherein the resulting source table has a unique element in each row of the first column.

23. The computer program product of claim 22 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

24. The computer program product of claim 21 wherein the comparison operation uses an equal comparison operator.

25. A computer program product including a medium usable by a processor, the medium having stored thereon a sequence of instructions, wherein when the sequence of instructions is executed by the processor, the processor executes a process for upserting a source table with a destination table, the process comprising:

selecting from a source table a first column comprising a plurality of elements;

selecting from a destination table a second column comprising a plurality of elements;

updating a row in the destination table with a row from the source table upon the success of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table; and

inserting a row from the source table into the destination table upon the failure of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table,

the process using no more than one query language statement.

26. The computer program product of claim 25 wherein the process further comprises combining the rows in the source table, wherein the resulting source table has a unique element in each row of the first column.

27. The computer program product of claim 26 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the element in the first column;

creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

28. The computer program product of claim 25 wherein the comparison operation uses an equal comparison operator.

29. A computer program product including a medium usable by a processor, the medium having stored thereon a sequence of instructions, wherein when the sequence of instructions is executed by the processor, the processor executes a process for aggregating data in a database, the process comprising:

parsing from a single command line, a command, a source table, a destination table, a source key, and a destination key;

comparing the source key in each row of the source table with the destination key in each row of the destination table;

determining a set of update rows based upon the success of a comparison operation performed on the source key and the destination key;

determining a set of insert rows based upon the failure of a comparison operation performed on the source key and the destination key;

updating the destination table with the set of update rows; and

inserting into the destination table the set of insert rows;

wherein no more than one command line is parsed.

30. The computer program product of claim 29 wherein the process further comprises combining the rows in the source table, wherein the resulting source table has a unique source key in each row of the source table.

31. The computer program product of claim 30 wherein combining the rows in the source table comprises:

sorting the rows in the source table based on the source key;

creating a group of rows, wherein each row in the group of rows contains a matching element in the source key; and

combining the group of rows into a single row.

32. The computer program product of claim 29 wherein the comparison operation uses an equal comparison operator.

33. A computer-implemented system for upserting a source table with a destination table, the system comprising:

logic for selecting from a source table a first column comprising a plurality of elements;

logic for selecting from a destination table a second column comprising a plurality of elements;

logic for updating a row in the destination table with a row from the source table upon the success of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table; and

logic for inserting a row from the source table into the destination table upon the failure of a comparison operation on an element in the first column of the row from the source table and an element in the second column of the row from the destination table,

the system using no more than one query language statement.

34. The system of claim 33 further comprising:

logic for combining the rows in the source table, wherein the resulting source table has a unique element in each row of the first column.

35. The system of claim 34 wherein the logic for combining the rows in the source table comprises:

logic for sorting the rows in the source table based on the element in the first column;

logic for creating a group of rows, wherein each row in the group of rows contains a matching element in the first column; and

combining the group of rows into a single row.

36. The system of claim 33 wherein the comparison operation uses an equal comparison operator.