Use of oraclemergeinto

When preparing a report, we often need to process the data updated several days ago due to the large amount of data and the time of Data uploading. In this case, we need to perform the UPDATE or INSERT operations, MERGEINTO is recommended at this time, but you need to pay attention to this problem. Let me first test: SQLselect * fromytrep. tab1; COL_ACOL_ B

When preparing a report, we often need to process the data updated several days ago due to the large amount of data and the time of Data uploading. In this case, we need to perform the UPDATE or INSERT operations, merge into is recommended at this time, but you need to pay attention to this problem. Let me first test: SQL select * from ytrep. tab1; COL_A COL_ B

When preparing a report, we often need to process the data updated several days ago due to the large amount of data and the time of Data uploading. In this case, we need to perform the UPDATE or INSERT operations, merge into is recommended at this time, but you need to pay attention to one problem at this time. Let's test it first:

SQL> select * from ytrep. tab1;

COL_A COL_ B COL_C
------------------------------
1
2 B
3 C
4 D
5 E
6 F
7G
8 H

8 rows selected

SQL> select * from ytrep. tab2;

COL_A COL_ B COL_C COL_D
----------------------------------------
1 A A1 1
1 A A1 2
2 B B1 1
3 C C1 1
4 D D1 1
5 E E1 2
5 E E2 1
6 F F1 1
6 F F1 2
9G G1 1
10 H H1 1

11 rows selected

In the preceding two tables, you must update the col_c value of ytrep. tab1 based on the values of col_a and col_ B in ytrep. tab2. Then, run the following SQL statement:

Merge into ytrep. tab1 p
Using (
Select t. col_A, t. col_ B, col_C
From ytrep. tab2 t
) Tmp
On (p. col_A = tmp. col_A and p. col_ B = tmp. col_ B)
When not matched then
Insert (col_A, col_ B, col_C) values (tmp. col_a, tmp. col_ B, tmp. col_C)
When matched then
Update set p. col_c = tmp. col_c;

Tip:

ORA-30926: unable to get a set of stable rows in the source table

ORA-30926: unable to get a stable set of rows in the source tables

Now let's analyze why this error occurs. In the ON condition, we can see p. col_A = tmp. col_A and p. col_ B = tmp. col_ B,

When the tmp table returns a record, ytrep. tab1: when the first record col_a = '1' and col_ B = 'A' is associated with the tmp table, it is found that tmp has two matching records, in this case, ORACLE cannot determine whether the COL_C value of the record in the TMP table should be A1 or A2 during update set. Then, ORACLE returns the above error;

At this time, we can infer that the value of the field involved in the ON condition returned in the TMP result set must be unique, otherwise the above ORA-30926 error must be reported;

At this time, someone may ask, what should I do?

There are two scenarios:

1. One is to obtain the MIN or MAX value by group by of the data in the TMP table;

Merge into ytrep. tab1 p
Using (
Select t. col_A, t. col_ B, max (col_C) col_C -- min (col_C) col_C
From ytrep. tab2 t
Group by t. col_a, t. col_ B
) Tmp
On (p. col_A = tmp. col_A and p. col_ B = tmp. col_ B)
When not matched then
Insert (col_A, col_ B, col_C) values (tmp. col_a, tmp. col_ B, tmp. col_C)
When matched then
Update set p. col_c = tmp. col_c;

SQL> select * from ytrep. tab1;

COL_A COL_ B COL_C
------------------------------
1 A A1
2 B B1
3 C C1
4 D D1
5 E E2
6 F F1
7G
8 H
10 H H1
9G G1

10 rows selected

2. Sort the values of the first or last row based on the column values after grouping;

Merge into ytrep. tab1 p
Using (
Select distinct t. col_A, t. col_ B, first_value (col_c) over (partition by t. col_a, t. col_ B order by t. col_d asc) col_c
From ytrep. tab2 t
) Tmp
On (p. col_A = tmp. col_A and p. col_ B = tmp. col_ B)
When not matched then
Insert (col_A, col_ B, col_C) values (tmp. col_a, tmp. col_ B, tmp. col_C)
When matched then
Update set p. col_c = tmp. col_c;

SQL> select * from ytrep. tab1;

COL_A COL_ B COL_C
------------------------------
1 A A1
2 B B1
3 C C1
4 D D1
5 E E2
6 F F1
7G
8 H
10 H H1
9G G1

10 rows selected

In the preceding two SQL statements, you can change the order by sorting method to compare the results.

The above is only my learning test results. If you have different opinions or good suggestions, you can leave a message. Thank you!