SQL> merge into book b
2 using book1 b1
3 on (b.bookid = b1.bookid)
4 when matched then
5 update set b.bookid = 'tp2001--001'
6 when not matched then
7 insert values (br.bookid, br.cardid, br.bdate, br.sdate);
merge into book b
*
第 1 行出现错误:
ORA-00947: 没有足够的值这是什么问题,神马情况啊
2 using book1 b1
3 on (b.bookid = b1.bookid)
4 when matched then
5 update set b.bookid = 'tp2001--001'
6 when not matched then
7 insert values (br.bookid, br.cardid, br.bdate, br.sdate);
merge into book b
*
第 1 行出现错误:
ORA-00947: 没有足够的值这是什么问题,神马情况啊
要么insert后面带上字段名insert (,,,,) values(,,,,)
book表的字段比insert的字段多br.bookid--br是哪里来的?
名称
-------------------------------------------------
BOOKID
BOOKNAME
EDITOR
PRICE
PUBLISH
PUBDATE
QTY
有7个字段,book1是用create table book1 as select * from book,写的
book七个字段,你values里面只有4个值,能不报错吗》
2 using book1 b1
3 on(b.bookid = b1.bookid)
4 when matched then
5 update set b.bookid = 'tp2001--001'
6 when not matched then
7 insert values (b1.bookid, b1.bookname,b1.editor,b1.price,b1.publish,b1.pubdate,b1.qty);
on(b.bookid = b1.bookid)
*
第 3 行出现错误:
ORA-38104: 无法更新 ON 子句中引用的列: "B"."BOOKID"
还是有错啊
SQL> merge into borrow b
2 using borrow1 b1
3 on(b.cardid = b1.cardid)
4 when matched then
5 update set b.bdate = '12-12月-2013'
6 when not matched then
7 insert values (b1.bookid,b1.cardid,b1.bdate,b1.sdate);
using borrow1 b1
*
第 2 行出现错误:
ORA-30926: 无法在源表中获得一组稳定的行
官方文档说的很清楚了:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606Restrictions on the merge_update_clause This clause is subject to the following restrictions:
You cannot update a column that is referenced in the ON condition clause.
You cannot specify DEFAULT when updating a view.
名称
-----------------------------------
BOOKID
CARDID
BDATE
SDATE
多看下官方文档,或者网上关于merge的专题吧。
merge也是有一些限制的。。