UPDATE UNIT U SET U.BASED_LOT_RRN = (SELECT L.BASED_LOT_RRN FROM LOT L WHERE U.LOT_RRN = L.lot_rrn)
Oracle,多表关联update,需要EXISTS
update UNIT_SP_TEST_PROG G set file_column = ‘VFBC’
where EXISTS (SELECT 1 from UNIT_SP_DATA A where A.TEST_PROGRAM_RRN=G.TEST_PROGRAM_RRN AND file_column = ‘VFBC(5.00mA)’ and A.unit_rrn in
(SELECT unit_rrn FROM unit where lot_rrn = 52232377))
update process_workflow_lot P set (P.ATTRIBUTE_DATA1,P.ATTRIBUTE_DATA2) =(
SELECT to_char(G.trans_end_timestamp,'yyyy-mm-dd hh24:mi:ss'),G.TRANS_PERFORMED_BY
FROM
LOT_TRANS_HISTORY H,
transaction_log G,
LOT_STEP_HISTORY SH
WHERE h.trans_rrn = g.trans_rrn
AND SH.STEP_SEQUENCE = H.STEP_SEQUENCE
AND SH.lot_rrn = H.lot_rrn
AND P.lot_rrn = SH.lot_rrn
AND P.product_rrn = SH.PRODUCT_RRN
AND P.PROCESS_RRN = SH.PROCESS_RRN
AND P.PROCESS_VERSION = SH.PROCESS_VERSION
--AND to_char(P.ROUTE_RRN) = SUBSTR(SH.PROCESS_STEP_VERSION, instr(SH.PROCESS_STEP_VERSION,'|')+1,instr(SH.PROCESS_STEP_VERSION,',',instr(SH.PROCESS_STEP_VERSION,'|'))-instr(SH.PROCESS_STEP_VERSION,'|')-1) -- 工序
AND instr(SH.PROCESS_STEP_VERSION, P.ROUTE_RRN || ',' || P.ROUTE_VERSION )>1 --工序相等,NAMED_OBJECT中instacne_rrn是主键,不必担心重复
AND P.OPERATION_RRN = SH.OPERATION_RRN
AND P.route_process_seq = SH.ROUTE_SEQ
AND P.OPERATION_ROUTE_SEQ = SH.OPERATION_SEQ
AND g.trans_id = 'MOVEIN'
)
where P.lot_rrn = 5177911