问一下UPDATE想用WHERE条件中某表的字段的值怎么写

[evaELLIS]

看一下一段SQL,我想在更新字段值时,用到WHERE条件中某个表的某个字段的值,应该怎么改,
下面的FT2.PLANSEQ,报错,00904

UPDATE ATP_MACTH_DETAIL MD
SET MD.SEQBDATE = sysdate,
MD.ATPSTAT = '2',
MD.ATPSPLNO = FT2.PLANSEQ,
MD.DLV1 = ''
WHERE exists (SELECT 1
FROM ATP_ACCREDITATION_FORET AF, ATP_FORE_T2 FT2
WHERE MD.OMSORDER = AF.OMSORDER
AND MD.OMSITEM = AF.OMSITEM
AND AF.MATNO = FT2.MATNO
AND AF.BATCH = FT2.BATCH
AND AF.POSNO = FT2.POSNO
AND MD.ATPSTAT = '1'
AND AF.STATUS = '2');

[大明湖畔_帅锅]

1：替换成子查询 2：百度merge into 看符合更新的要求不

[fw0124]

改成MD.ATPSPLNO = ATP_FORE_T2.PLANSEQ?

[洋小葱]

同意一楼的，换成子查询吧!

[liubin041102]

update xxx set (t.a,t.b,t.c) = (select a,b,c from yyy,zzz where ``````) where exists (select 1 from from yyy,zzz where ``````)

[翁少]

UPDATE ATP_MACTH_DETAIL MD SET MD.SEQBDATE = sysdate, MD.ATPSTAT = '2', MD.ATPSPLNO = (SELECT FT2.PLANSEQ FROM ATP_ACCREDITATION_FORET AF, ATP_FORE_T2 FT2 WHERE MD.OMSORDER = AF.OMSORDER AND MD.OMSITEM = AF.OMSITEM AND AF.MATNO = FT2.MATNO AND AF.BATCH = FT2.BATCH AND AF.POSNO = FT2.POSNO AND MD.ATPSTAT = '1' AND AF.STATUS = '2') MD.DLV1 = '' ;