merge into 错误

[vstian]

merge into A a using B b
on(a.id_no = b.id_no)
when matched then update set a.sm_code=b.sm_code;


这句话报错 说缺少关键字???怎么修改阿

[ojuju10]

merge into A a using B b
on(a.id_no = b.id_no)
when matched then update set a.sm_code=b.sm_code;

语法没有错误，请问你的数据库版本是否为10g以及以上呢？

[vstian]

9i 的哈 ,9i 能用吗 ???

[小灰狼W]

语法没问题
merge into 是9i里引入的

[vstian]

9i 能使用merge into 吗


要是没有的话怎么执行这种功能 哈 ????

[ojuju10]

我仔细核对了语法真的没有问题

是在不行，就用子查询update吧

[shiyiwan]

9i用还要加when not matched then ....

update A a set a.sm_code = (select b.sm_code from B b where a.id_no = b.id_no ) where exists
(select 1 from B c where a.id_no = c.id_no );

[小灰狼W]

[Quote=引用 6 楼 shiyiwan 的回复:]
9i用还要加when not matched then ....

update A a set a.sm_code = (select b.sm_code from B b where a.id_no = b.id_no ) where exists
(select 1 from B c where a.id_no = c.id_no );
[/Quote]
楼主就试试加when not matched吧
想当初我就是通过merge into 认识shiyiwan的，嘿嘿

[vstian]

update A a
set a.sm_code= (select sm_code from B b where a.id_no=b.id_no);


改成这样功能是一样的吗 ????

[小灰狼W]

[Quote=引用 8 楼 vstian 的回复:]
update A a
set a.sm_code= (select sm_code from B b where a.id_no=b.id_no);


改成这样功能是一样的吗 ????
[/Quote]
要加个条件
update A a
set a.sm_code= (select sm_code from B b where a.id_no=b.id_no)
where exists(select 1 from b where id_no=a.id_no);

[shiyiwan]

后面要加exists判断的，否则子查询结果为NULL时会把a.sm_code也更新掉的
[Quote=引用 8 楼 vstian 的回复:]
update A a
set a.sm_code= (select sm_code from B b where a.id_no=b.id_no);


改成这样功能是一样的吗 ????
[/Quote]

[shiyiwan]

汗。。。
[Quote=引用 7 楼 wildwave 的回复:]

楼主就试试加when not matched吧
想当初我就是通过merge into 认识shiyiwan的，嘿嘿
[/Quote]

[ojuju10]

[Quote=引用 8 楼 vstian 的回复:]
update A a
set a.sm_code= (select sm_code from B b where a.id_no=b.id_no);


改成这样功能是一样的吗 ????
[/Quote]
加上where条件，
where a.id_no in (select id_no from b )
否则不满足条件的数据也被更新为null

[vstian]

多谢各位了