merge into 更新和插入

近期在进行熟悉代码和进行优化的时候，遇到了业务员提出的一个问题，报的错误。

### Error updating database Cause: java.sql.sQLException: ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."SCHEME_ID" ### The error may exst in file (../mapper/ImplPersonItemMapper.xm] 
### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: 

MERGE INTO SCHEME_IMPL_PERSON_ITEM A USING 
	(SELECT * FROM SCHEME_IMPL_PERSON_ITEM WHERE SCHEME_ID = ?  AND USER_CODE = ? AND TARGET_ID = ? ) b
	ON A.SCHEME_ID = b.SCHEME_ID AND A.USER_CODE = b.USER_CODE AND A.TARGET_ID = B.TARGET_ID 
	WHEN MATCHED THEN  
        UPDATE SET SCHEME_ID = ?, USER_NAME = ?, USER_ID = ? 
        WHERE SCHEME_ID = ? AND USER_CODE = ? AND TARGET_ID = ? 
	WHEN NOT MATCHED THEN 
		INSERT (DEPT_ID,USER_ID,USER_NAME) VALUES (?,?,?)

### Cause: java.sq.sQLException: ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."SCHEME_ID" , uncategorized sQLException; SQL state [99999]: error code [38104] ORA-38104:
Columns referenced in the ON Clause cannot be updated: "A".""SCHEME_ID"; nested exception is java.sgl.SQLException:ORA-38104: Columns referenced in the ON Clause cannot be updated: "A"."SCHEME_ID"
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注：因为需要保密，不能过多的完全粘贴全部错误信息，但是去掉的也是部分字段，不影响目前问题的叙述。

1、介绍一下 Oracle ORA-38104 错误。

最开始我没有弄清楚这个错误是什么意思，所以就去百度搜索。首先按搜索了 SQL state [99999]。后来又去搜索了一下 ORA-38104 是什么意思。百度得到的解释是：Oracle ORA-38104：ON 子语句中引用的列不能更新。

什么是 ORA-38104 错误？

ORA-38104 是 Oracle 数据库中的一个错误代码，它表示在 update 语句中引用了 on 子句中的列，而这些列是不能被更新的。当我们尝试更新这些列时，Oracle 就会报 ORA-38104 错误。

上面是我在百度上面搜索到的，当然我自己肯定没能解释详细，有兴趣的可以搜索一下。

上面的解释给我的理解就是：

在上面错误的 sql 当中，on 后面出现的字段是不能出现在 update 语句里面的。

---

2、介绍一下 merge into 语句。

merge into ，简单来说就是判断表中有没有符合 on() 条件中的数据，有了就更新数据，没有就插入数据。

注：我是使用了错误的语句当中“?”，真实当中需要传入的 mapper 当中的变量值

2.1 当只有 update 的时候。

merge into SCHEME_IMPL_PERSON_ITEM A using
	(SELECT * FROM SCHEME_IMPL_PERSON_ITEM WHERE SCHEME_ID = ?  AND USER_CODE = ? AND TARGET_ID = ? ) B
   on (A.SCHEME_ID = b.SCHEME_ID AND A.USER_CODE = b.USER_CODE AND A.TARGET_ID = B.TARGET_ID)
   WHEN MATCHED THEN  
     UPDATE SET SCHEME_ID = ?, USER_NAME = ?, USER_ID = ? 
     WHERE SCHEME_ID = ? AND USER_CODE = ? AND TARGET_ID = ? 
1
2
3
4
5
6

此时上面的语句当中是只有 update 的，且 on 的条件后面是有 update 需要更改的字段。所以可以把在 on 里面需要的条件放到 update 的子句 where 后面。

merge into SCHEME_IMPL_PERSON_ITEM A using
	(SELECT * FROM SCHEME_IMPL_PERSON_ITEM WHERE SCHEME_ID = ?  AND USER_CODE = ? AND TARGET_ID = ? ) B
   on (A.USER_CODE = b.USER_CODE AND A.TARGET_ID = B.TARGET_ID)
   WHEN MATCHED THEN  
     UPDATE SET SCHEME_ID = ?, USER_NAME = ?, USER_ID = ? 
     WHERE SCHEME_ID = ? AND USER_CODE = ? AND TARGET_ID = ? AND A.SCHEME_ID = b.SCHEME_ID
1
2
3
4
5
6

这样是能够完成更新的。

2.2 当语句里面有 insert 的时候

merge into SCHEME_IMPL_PERSON_ITEM A using
	(SELECT * FROM SCHEME_IMPL_PERSON_ITEM WHERE SCHEME_ID = ?  AND USER_CODE = ? AND TARGET_ID = ? ) B
   on (A.SCHEME_ID = b.SCHEME_ID AND A.USER_CODE = b.USER_CODE AND A.TARGET_ID = B.TARGET_ID)
   WHEN MATCHED THEN  
     UPDATE SET SCHEME_ID = ?, USER_NAME = ?, USER_ID = ? 
     WHERE SCHEME_ID = ? AND USER_CODE = ? AND TARGET_ID = ?
   WHEN NOT MATCHED THEN 
		INSERT (DEPT_ID,USER_ID,USER_NAME) VALUES (?,?,?)
1
2
3
4
5
6
7
8

这个时候是属于更新或插入同一张表里面，再次使用上面的方法的时候就不会成功了所以需要稍稍更改一下 sql

merge into SCHEME_IMPL_PERSON_ITEM A using
	(SELECT ? SCHEME_ID, ? USER_CODE, ? USER_CODE FROM dual) B
   on (A.SCHEME_ID = b.SCHEME_ID AND A.USER_CODE = b.USER_CODE AND A.TARGET_ID = B.TARGET_ID)
   WHEN MATCHED THEN  
     UPDATE SET USER_NAME = ?, USER_ID = ? 
     WHERE SCHEME_ID = ? AND USER_CODE = ? AND TARGET_ID = ?
   WHEN NOT MATCHED THEN 
		INSERT (DEPT_ID,USER_ID,USER_NAME) VALUES (?,?,?)
1
2
3
4
5
6
7
8

这样的话，就可以实现有则更新，无则新增的需求。

以上只是属于我这个初学者的一些记录，能够解决掉您的问题更好，属于个人纪录日常问题！！！