oracle merge 博客分类： oracle oracle

MERGE INTO EHR_STAFF_ATTEND_DETAIL A 
USING (SELECT 'LOVE001' AS STAFF_ID,
 2 AS ATTEND_DETAIL_ID,
 '1011' AS ATTEND_NO, 
'02' AS ATTEND_STYLE, 
'41' AS PERMUTE_ID, 
'1' AS IS_VALID, 
USER AS CREATED_BY,
 USER AS UPDATED_BY, 
SYSDATE AS DATE_CREATED, 
SYSDATE AS DATE_UPDATED 
FROM DUAL) B ON (A.STAFF_ID = B.STAFF_ID) 
WHEN MATCHED THEN
 UPDATE SET 
A.ATTEND_NO = B.ATTEND_NO, 
A.ATTEND_STYLE = B.ATTEND_STYLE, 
A.PERMUTE_ID = B.PERMUTE_ID, 
A.IS_VALID = B.IS_VALID, 
A.UPDATED_BY = B.UPDATED_BY, 
A.DATE_UPDATED = B.DATE_UPDATED 
WHEN NOT MATCHED THEN 
INSERT (A.ATTEND_DETAIL_ID,A.STAFF_ID,A.ATTEND_NO,A.ATTEND_STYLE,A.PERMUTE_ID,A.IS_VALID,A.CREATED_BY,A.UPDATED_BY,A.DATE_CREATED,A.DATE_UPDATED) 
VALUES (B.ATTEND_DETAIL_ID,B.STAFF_ID,B.ATTEND_NO,B.ATTEND_STYLE,B.PERMUTE_ID,B.IS_VALID,B.CREATED_BY,B.UPDATED_BY,B.DATE_CREATED,B.DATE_UPDATED);

 MERGE 使用