ORA-30926: unable to get a stable set of rows in the source tables
来源:互联网 发布:淘宝店铺女装名字 编辑:程序博客网 时间:2024/05/16 13:07
Oracle 中用一个表的数据更新另一个表的数据
最好的方法是用merge语法:
- merge into tab1
- using tab2
- on(tab1.id=tab2.id)
- when matched then
- update set tab1.val = tab2.val
比如在tab2中再插入一条 insert into tab2 values(2,'xxxx')
可以通过在using中的subquery中将重复记录过滤来避免这种错误,
merge:
- merge into tab1
- using (select * FROM tab2 X WHERE X.ROWID =
- (SELECT MAX(Y.ROWID) FROM tab2 Y WHERE X.ID = Y.ID)) tab2
- on(tab1.id=tab2.id)
- when matched then
- update set tab1.val = tab2.val
但是考虑到关联查询效率问题,可以使用oracle分析函数去重后merge
MERGE INTO YTRPT.T_EXP_CHECK_STATION P
USING (SELECT ORG_CODE, WAYBILL_NO
FROM (SELECT K.ORG_CODE,
K.WAYBILL_NO,
ROW_NUMBER() OVER(PARTITION BY K.WAYBILL_NO ORDER BY K.CREATE_TIME DESC) AS RW --去除重复取出最新的那条记录,否则merge会出错
FROM YTEXP.T_EXP_OP_RECORD_HANDON K
WHERE K.CREATE_TIME >= P_START_TIME
AND K.CREATE_TIME < P_END_TIME + 10
AND K.OP_CODE IN ('710', '711') --派件扫描
AND K.STATUS > 0
AND K.AUX_OP_CODE <> 'DELETE')
WHERE RW = 1) TEMP
ON (P.WAYBILL_NO = TEMP.WAYBILL_NO AND P.RPT_DATE = P_START_TIME)
WHEN MATCHED THEN
UPDATE SET P.SIG_ORG_CODE = TEMP.ORG_CODE;
- ORA-30926: unable to get a stable set of rows in the source tables
- ORA-30926: unable to get a stable set of rows in the source tables
- ORA-30926: unable to get a stable set of rows in the source tables
- ORACLE MERGE INTO语句,unable to get a stable set of rows in the source tables报错解决
- get all rows count of all tables in a mysql database.
- How to get the HTML source of a webpage in Ruby
- 转:How to submit the rows of a repeating table in InfoPath to a SharePoint list
- Get the rows count of all the table in Sybase
- a method to set the title of webpart in code
- vmware vcenter converter 4 FAILED: Unable to create a VSS snapshot of the source volume(s)
- How to get the tables of one schema?
- How to provide the capability to expand empty rows of a master view to be able to add child rows
- how to get the space size of some tables in one database?
- How to get request_id of previous request in a request set?
- How to get a notification from Linux when the set of network interfaces changes
- How to set the ListView Rows Height
- How to Get the Frequency Table of a Categorical Variable as a Data Frame in R
- A guide to get you building OGRE from source with the default settings in the shortest possible time
- MY--jaxws-webservice编程第一天
- oracle 导入 导出 简单命令
- 三个故事
- ZedGraph 教程
- Android基于XMPP Smack Openfire下学习开发IM(一)实现用户注册、登录、修改密码和注销等
- ORA-30926: unable to get a stable set of rows in the source tables
- Python编程规范
- winform调用javascript
- 公司搭建maven平台环境准备(一)
- mysql 数据库备份
- 【问题】bean.backconnectbean cannot be cast to bean.UserBean
- ASP.NET的SEO(七):.NET 4中的SEO改进.
- 设计模式_模板方法模式
- MAC 彻底清倒废纸娄中的内容