The Oracle database, MERGE into statement, often appears ora-30926:unable to get a stable set of rows in the source tables this error, as shown in:
After checking, this error is caused by data duplication in the data source table (that is, the table behind the FROM keyword). In the actual project development, we generally can not arbitrarily change the data table record, then how to avoid this error and how to correct the error?
Take a look at the following sql:
MERGE into Temp_zl_accountlist t1using (select- row_number () over (partition by A.screen_name order by a. user_id desc) Rd, a.*, a.rowid row_id from pprt. T_bp_acl_user a) where rd = 1) t2on (t1. Login_no = t2. Screen_name) when matched and then UPDATE SET t1.user_id = t2.user_id;
the SQL uses the Row_number () function to sort the duplicate records, and then the subquery takes only rd=1 rows, so that the subquery generates only one record, and then executes the statement, and then it is easy to rest, and no longer afraid of data source table repeat data error hint AH.