Live SQL

Simulating ORA-30926 on 19c

View All Public Scripts Login to Run Script

Script Name Simulating ORA-30926 on 19c
Visibility Unlisted - anyone with the share link can access
Description This demo simulated how to use oracle merge by updating the target table based on the sql query of source table, next demonstrated how to generate ORA-30926 error when the sql query of source table exists the duplicated data. Finally we use 4 number of rewriting methods to eliminiate ORA-30926 error, which is making merge sql statement to run smoothly.
Area SQL General
Contributor Quanwen Zhao
Created Wednesday May 28, 2025

Statement 1
```
drop table emps purge
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

drop table corrects purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 3

create table emps ( 
id number primary key, 
fname varchar2(15), 
lname varchar2(15) 
)

Table created.

Statement 4

insert into emps values (1, 'quanwen', 'Zheng')

1 row(s) inserted.

Statement 5

insert into emps values (2, 'nin', 'jin')

1 row(s) inserted.

Statement 6
```
commit
```
```
1 row(s) inserted.
```

Statement 7

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 8

create table corrects (
emp_id number,
fname varchar2(15),
lname varchar2(15),
reviewed  date
)

1 row(s) inserted.

Statement 9

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate)

Table created.

Statement 10

insert into corrects values (2, 'Ning', 'Jin', sysdate)

Statement processed.

Statement 11
```
commit
```

Statement 12

select * from corrects


EMP_ID FNAME LNAME REVIEWED
1 Quanwen Zhao 28-MAY-25
2 Ning Jin 28-MAY-25

2 rows selected.

Statement 13

Merge statement runs successfully.

merge into emps target 
using (select emp_id, fname, lname 
       from corrects 
) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

Statement processed.

Statement 14

We acquired the expected data from the target table emps.

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 15
```
rollback
```
```
Statement processed.
```

Statement 16

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 17

Making the target table emps to become the original data.

update emps set fname='quanwen', lname='Zheng' where id = 1

1 row(s) updated.

Statement 18

Making the target table emps to become the original data.

update emps set fname='nin', lname='jin' where id = 2

1 row(s) updated.

Statement 19
```
commit
```
```
Statement processed.
```

Statement 20

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 21

We built some non unique data to the source table corrects.

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate-1)

1 row(s) inserted.

Statement 22

We built some non unique data to the source table corrects.

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate-2)

1 row(s) inserted.

Statement 23

We built some non unique data to the source table corrects.

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate-3)

1 row(s) inserted.

Statement 24

We built some non unique data to the source table corrects.

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate-4)

1 row(s) inserted.

Statement 25

We built some non unique data to the source table corrects.

insert into corrects values (1, 'Quanwen', 'Zhao', sysdate-5)

1 row(s) inserted.

Statement 26

We built some non unique data to the source table corrects.

insert into corrects values (2, 'Ning', 'Jin', sysdate-1)

1 row(s) inserted.

Statement 27

We built some non unique data to the source table corrects.

insert into corrects values (2, 'Ning', 'Jin', sysdate-2)

1 row(s) inserted.

Statement 28

We built some non unique data to the source table corrects.

insert into corrects values (2, 'Ning', 'Jin', sysdate-3)

1 row(s) inserted.

Statement 29

We built some non unique data to the source table corrects.

insert into corrects values (2, 'Ning', 'Jin', sysdate-4)

1 row(s) inserted.

Statement 30

We built some non unique data to the source table corrects.

insert into corrects values (2, 'Ning', 'Jin', sysdate-5)

1 row(s) inserted.

Statement 31
```
commit
```
```
Statement processed.
```

Statement 32

select * from corrects order by 1, 4 desc


EMP_ID FNAME LNAME REVIEWED
1 Quanwen Zhao 28-MAY-25
1 Quanwen Zhao 27-MAY-25
1 Quanwen Zhao 26-MAY-25
1 Quanwen Zhao 25-MAY-25
1 Quanwen Zhao 24-MAY-25
1 Quanwen Zhao 23-MAY-25
2 Ning Jin 28-MAY-25
2 Ning Jin 27-MAY-25
2 Ning Jin 26-MAY-25
2 Ning Jin 25-MAY-25
2 Ning Jin 24-MAY-25
2 Ning Jin 23-MAY-25

12 rows selected.

Statement 33

Because there exists the duplicated data on the sql query ("select emp_id, fname, lname from corrects") of source table corrects , now merge statement returned the ORA-30926, which makes the update action failed.

merge into emps target 
using (select emp_id, fname, lname 
       from corrects 
) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 34

Now the target table emps has still been the original data.

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 35

Using distinct to rewrite the sql query of source table corrects.

Rewriting method 1:

select distinct emp_id, fname, lname from corrects order by 1


EMP_ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 36

Using the previous block 35's query to rewrite the using () section of merge statement. Wow, great, running successfully.

Rewriting method 1:

merge into emps target 
using (select distinct emp_id, fname, lname from corrects order by 1) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

Statement processed.

Statement 37

We acquired the expected data on the target table emps.

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 38

Making the target table emps to become the original data.

update emps set fname='quanwen', lname='Zheng' where id = 1

1 row(s) updated.

Statement 39

Making the target table emps to become the original data.

update emps set fname='nin', lname='jin' where id = 2

1 row(s) updated.

Statement 40
```
commit
```
```
Statement processed.
```

Statement 41

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 42

Using group by and max function to rewrite the sql query of source table corrects.

Rewriting method 2:

select emp_id, max(fname) fname, max(lname) lname from corrects 
group by emp_id 
order by 1


EMP_ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 43

Using the previous block 42's query to rewrite the using () section of merge statement. Wow, great, running successfully.

Rewriting method 2:

merge into emps target 
using (select emp_id, max(fname) fname, max(lname) lname from corrects 
       group by emp_id 
       order by 1 
      ) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

Statement processed.

Statement 44

We acquired the expected data on the target table emps.

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 45

Making the target table emps to become the original data.

update emps set fname='quanwen', lname='Zheng' where id = 1

1 row(s) updated.

Statement 46

Making the target table emps to become the original data.

update emps set fname='nin', lname='jin' where id = 2

1 row(s) updated.

Statement 47
```
commit
```
```
Statement processed.
```

Statement 48

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 49

Using the analytic function row_number() to rewrite the sql query of source table corrects.

Rewriting method 3:

select * from ( 
select emp_id, fname, lname, row_number() over(partition by emp_id order by emp_id) rn from corrects 
）where rn=1


EMP_ID FNAME LNAME RN
1 Quanwen Zhao 1
2 Ning Jin 1

2 rows selected.

Statement 50

Using the previous block 49's query to rewrite the using () section of merge statement. Wow, great, running successfully.

Rewriting method 3:

merge into emps target 
using 
( select * from 
  ( select emp_id, fname, lname, row_number() over(partition by emp_id order by emp_id) rn from corrects 
  ）where rn=1 
) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

Statement processed.

Statement 51

We acquired the expected data on the target table emps.

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 52

Making the target table emps to become the original data.

update emps set fname='quanwen', lname='Zheng' where id = 1

1 row(s) updated.

Statement 53

Making the target table emps to become the original data.

update emps set fname='nin', lname='jin' where id = 2

1 row(s) updated.

Statement 54
```
commit
```
```
Statement processed.
```

Statement 55

select * from emps


ID FNAME LNAME
1 quanwen Zheng
2 nin jin

2 rows selected.

Statement 56

Using the rowid, group by and max(rowid) to rewrite the sql query of source table corrects.

Rewriting method 4:

select emp_id, fname, lname from corrects where rowid in (select max(rowid) from corrects group by emp_id) order by emp_id


EMP_ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Statement 57

Using the previous block 56's query to rewrite the using () section of merge statement. Wow, great, running successfully.

Rewriting method 4:

merge into emps target 
using 
( select emp_id, fname, lname 
  from corrects 
  where rowid in (select max(rowid) from corrects group by emp_id) 
  order by emp_id 
) source 
on (target.id = source.emp_id) 
when matched then 
update set 
target.fname = source.fname, 
target.lname = source.lname

Statement processed.

Statement 58

We acquired the expected data on the target table emps.

select * from emps


ID FNAME LNAME
1 Quanwen Zhao
2 Ning Jin

2 rows selected.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database