2,加个条件就是了:
UPDATE KCH05_TEST SET KCCKSL=(SELECT B.KCCZSL FROM KCH6SUM05Y B WHERE kch05_test.XTDWDM=B.XTDWDM AND kch05_test.XTHSQM=B.XTHSQM AND kch05_test.KCCKDM=B.KCCKDM AND kch05_test.KCWPDM=B.KCWPDM)
where exists(SELECT * FROM KCH6SUM05Y B WHERE kch05_test.XTDWDM=B.XTDWDM AND kch05_test.XTHSQM=B.XTHSQM AND kch05_test.KCCKDM=B.KCCKDM AND kch05_test.KCWPDM=B.KCWPDM)
UPDATE KCH05_TEST SET KCCKSL=(SELECT B.KCCZSL FROM KCH6SUM05Y B WHERE kch05_test.XTDWDM=B.XTDWDM AND kch05_test.XTHSQM=B.XTHSQM AND kch05_test.KCCKDM=B.KCCKDM AND kch05_test.KCWPDM=B.KCWPDM)
where exists(SELECT * FROM KCH6SUM05Y B WHERE kch05_test.XTDWDM=B.XTDWDM AND kch05_test.XTHSQM=B.XTHSQM AND kch05_test.KCCKDM=B.KCCKDM AND kch05_test.KCWPDM=B.KCWPDM)
----------------------------------------
不是吧,DB2难道不支持这么简单的语法?(2)按风云的写法了
第一种写法在DB2 里不行,提示:“未定义列限定词或表 B ”huwei2003(凡) :
看不懂,什么意思啊?
UPDATE KCH05_TEST SET KCCKSL=B.KCCZSL FROM KCH05_TEST A Inner JOIN KCH6SUM05Y B ON A.XTDWDM=B.XTDWDM AND A.XTHSQM=B.XTHSQM AND A.KCCKDM=B.KCCKDM AND A.KCWPDM=B.KCWPDM AND A.KCCKSL<>B.KCCZSL
这不就是我的第一种写法么?
我想问的是UPDATE A SET A.KCCKSL=(SELECT B.KCCZSL。)这种写法以前见过的好像不用在句尾加 where exists 子句,可今天如果不加该子句则不能通过,提示:
“无法将 NULL 值插入列 'KCCKSL',表 'KCH05_TEST';该列不允许空值。UPDATE 失败。”另外,(1)第一种方法在DB2环境下怎么写?若有人知道小弟另开贴送50分
(注:CSDN DB2那版没人气,不想在那问)
KCH6SUM05Y B ON A.XTDWDM=B.XTDWDM AND A.XTHSQM=B.XTHSQM AND A.KCCKDM=B.KCCKDM AND A.KCWPDM=B.KCWPDM AND A.KCCKSL<>B.KCCZSL
UPDATE KCH05_TEST SET KCCKSL=B.KCCZSL FROM KCH05_TEST A Inner JOIN KCH6SUM05Y B ON A.XTDWDM=B.XTDWDM AND A.XTHSQM=B.XTHSQM AND A.KCCKDM=B.KCCKDM AND A.KCWPDM=B.KCWPDM
則稍改一點
UPDATE KCH05_TEST SET KCCKSL=B.KCCZSL FROM KCH05_TEST A Inner JOIN KCH6SUM05Y B ON A.XTDWDM=B.XTDWDM AND A.XTHSQM=B.XTHSQM AND A.KCCKDM=B.KCCKDM AND A.KCWPDM=B.KCWPDM
where B.KCCZSL is not null
UPDATE KCH05_TEST SET KCCKSL=B.KCCZSL FROM KCH05_TEST A, KCH6SUM05Y B where A.XTDWDM=B.XTDWDM AND A.XTHSQM=B.XTHSQM AND A.KCCKDM=B.KCCKDM AND A.KCWPDM=B.KCWPDM AND A.KCCKSL<>B.KCCZSL
Inner Join 和 Join 都是内联接嘛,一个道理啊,和我第一种写法一样的嘛
第一种写法在MS-Sqlserver中已测试通过了,只是想将这种写法应用到DB2中去却不能通过
我用你的方法在MS-Sqlserver中已测试通过,可在DB2中测试还是没有通过(提示:表 KCH05_test 的列 KCCKSL 中不允许有空值)
我的最终目的是要DB2中完成这种更新,老大你还有其它招么?
谢谢,你的方法在MS-SQLSERVER中可以通过,不过不是我想要的