我想把下面
SELECT a.cb06 e06,a.cb17 e17,a.cb08 e08 FROM cb a,cu b
WHERE b.cu01=a.cb01 AND b.cu05=a.cb03 AND b.cu01 LIKE 'FF%' AND b.cu01 NOT LIKE 'FF%-LH' AND a.cb06= 'G06'语句里查询得出的结果全部更新为e06='G47',E17='abcd',e08='W05'应该用怎样的语句?
我用下面的语句UPDATE (SELECT a.cb06 e06,a.cb17 e17,a.cb08 e08 FROM cb a,cu b
WHERE b.cu01=a.cb01 AND b.cu05=a.cb03 AND b.cu01 LIKE 'FF%' AND b.cu01 NOT LIKE 'FF%-LH' AND a.cb06= 'G06' )
SET e06='G47',E17='abcd',e08='W05'执行时,总是提示:ORA-01779:cannot modify a column which maps to a non key-preserved table
查不到语句,请大家帮忙,不知道应该怎样写。
UPDATE CB
SET CB06 = 'G47', CB17 = 'abcd', CB08 = 'W05'
WHERE ROWID IN (SELECT ROWID AS ROW_ID
FROM CB A, CU B
WHERE B.CU01 = A.CB01
AND B.CU05 = A.CB03
AND B.CU01 LIKE 'FF%'
AND B.CU01 NOT LIKE 'FF%-LH'
AND A.CB06 = 'G06')
;
--漏了一个A. 更正一下
SELECT A.ROWID AS ROW_ID
set a.cb06='G47', a.cb17='abcd', a.cb08='W05'
where exists (select 1 from cu b
where b.cu01=a.cb01
AND b.cu05=a.cb03
AND b.cu01 LIKE 'FF%'
AND b.cu01 NOT LIKE 'FF%-LH'
)
AND a.cb06= 'G06'
谢谢!
执行的时候提示下面的错误,是怎么回事。
ORA-00918:column ambiguously defined
执行时提示 invalid character