WHEN MATCHED THEN
UPDATE
SET p.product = b.product when not ~~~~~
insert
(******)values(*****)
原本是没问题,但数据量大了后,我想看哪条纪录在该执行中被修改或添加了就没办法了,于是加了个字段,做了些改动:
WHEN MATCHED THEN
UPDATE
SET p.product = b.product ,
p.uptime = sysdatewhen not ~~~~~
insert
(****** uptime)values(***** sysdate)这样改了以后就报错了
unable to get a stable set of rows in the source tables求解
UPDATE
SET p.product = b.product when not ~~~~~
insert
(******)values(*****)
原本是没问题,但数据量大了后,我想看哪条纪录在该执行中被修改或添加了就没办法了,于是加了个字段,做了些改动:
WHEN MATCHED THEN
UPDATE
SET p.product = b.product ,
p.uptime = sysdatewhen not ~~~~~
insert
(****** uptime)values(***** sysdate)这样改了以后就报错了
unable to get a stable set of rows in the source tables求解
解决方案 »
- 数据库awr报告分析求教!
- 请帮我看看这个SQL怎样写
- rman怎么恢复控制文件???
- 服务器突然断电,ORACLE提示ORA-01033错误.在下很急,请各位兄弟姐妹帮忙解决一下.
- java调用hibernate去查询oracle数据库,其中:冒号问题,总是将冒号和后面的数认为是入参
- 关于excel如何导入oracle数据库问题
- crontab 定时维护oracle的问题?
- 出现意外 open错误,怎么解决呀?!!!
- 谁能说说在oracle,mysql等数据库中varchar类型的字段,到底是怎么存储的!?
- 请教oracle问题,急,在线等!
- Oracle 里而且没有SqlServer新建数据库的概念.
- 关于oracle备份恢复问题(oracle 11g2)?
SQL> select * from t_merge_a;
ID NAME CREATEDATE
----------- ---------- -----------
1 test 2010-9-27
SQL> select * from t_merge_b;
ID NAME
----------- ----------
1 test
2 just
SQL> select id,name,to_char(createdate,'yyyy-mm-dd hh24:mi:ss') from t_merge_a;
ID NAME TO_CHAR(CREATEDATE,
----------- ---------- ------------------------------
2 just 2010-09-27 14:18:19
1 test 2010-09-27 14:18:19
SQL>
SQL> select * from t_merge_a;
ID NAME CREATEDATE
----------- ---------- -----------
1 test 2010-9-27
SQL> select * from t_merge_b;
ID NAME
----------- ----------
1 test
2 just
SQL>
SQL> merge into t_merge_a a
2 using t_merge_b b
3 on (a.id=b.id and a.name=b.name)
4 when matched then update set a.createdate=sysdate
5 when not matched then insert(a.id,a.name,a.createdate)
6 values(b.id,b.name,sysdate);
Done
SQL> select id,name,to_char(createdate,'yyyy-mm-dd hh24:mi:ss') from t_merge_a;
ID NAME TO_CHAR(CREATEDATE,
----------- ---------- ------------------------------
2 just 2010-09-27 14:18:19
1 test 2010-09-27 14:18:19
SQL>
2 using t_merge_b b
3 on (a.id=b.id )
4 when matched then update set a.createdate=sysdate,a.name=b.name
5 when not matched then insert(a.id,a.name,a.createdate)
6 values(b.id,b.name,sysdate);
Done
SQL> select id,name,to_char(createdate,'yyyy-mm-dd hh24:mi:ss') from t_merge_a;
ID NAME TO_CHAR(CREATEDATE,'YYYY-MM-DD
----------- ---------- ------------------------------
2 just 2010-09-27 14:34:19
1 test 2010-09-27 14:34:19
SQL>
因为你匹配的时候数据不是一一对应.看下面的数据,t_merge_b里id=2的数据有两条时也会报你说的错误.
SQL> merge into t_merge_a a
2 using t_merge_b b
3 on (a.id=b.id )
4 when matched then update set a.createdate=sysdate,a.name=b.name
5 when not matched then insert(a.id,a.name,a.createdate)
6 values(b.id,b.name,sysdate);
Done
SQL> select id,name,to_char(createdate,'yyyy-mm-dd hh24:mi:ss') from t_merge_a;
ID NAME TO_CHAR(CREATEDATE,'YYYY-MM-DD
----------- ---------- ------------------------------
2 just 2010-09-27 14:34:19
1 test 2010-09-27 14:34:19
SQL> select id,name from t_merge_b;
ID NAME
----------- ----------
2 just
1 test
2 just
SQL>
SQL> merge into t_merge_a a
2 using t_merge_b b
3 on (a.id=b.id )
4 when matched then update set a.createdate=sysdate,a.name=b.name
5 when not matched then insert(a.id,a.name,a.createdate)
6 values(b.id,b.name,sysdate);
merge into t_merge_a a
using t_merge_b b
on (a.id=b.id )
when matched then update set a.createdate=sysdate,a.name=b.name
when not matched then insert(a.id,a.name,a.createdate)
values(b.id,b.name,sysdate)
ORA-30926: unable to get a stable set of rows in the source tables
SQL>