DELETE 명령어에 조인을 사용하여 처리하기

 

 

DELETE SCENE_MOUT FROM SCENE_MOUT [S]
LEFT JOIN SCENE_MINP [S1] on S.INP_NO = S1.INP_NO
LEFT JOIN MATERIAL_STOCK_OUT [S2] on S2.OUT_NO = S1.OUT_NO
WHERE S2.LOTNO IS NULL

 

여기에 트랜잭션까지 같이 하면

 

BEGIN TRAN

 

SELECT COUNT(*) FROM SCENE_MOUT [S]
LEFT JOIN SCENE_MINP [S1] on S.INP_NO = S1.INP_NO
LEFT JOIN MATERIAL_STOCK_OUT [S2] on S2.OUT_NO = S1.OUT_NO
WHERE S2.LOTNO IS NULL

 

DELETE SCENE_MOUT FROM SCENE_MOUT [S]
LEFT JOIN SCENE_MINP [S1] on S.INP_NO = S1.INP_NO
LEFT JOIN MATERIAL_STOCK_OUT [S2] on S2.OUT_NO = S1.OUT_NO
WHERE S2.LOTNO IS NULL

 

SELECT COUNT(*) FROM SCENE_MOUT [S]
LEFT JOIN SCENE_MINP [S1] on S.INP_NO = S1.INP_NO
LEFT JOIN MATERIAL_STOCK_OUT [S2] on S2.OUT_NO = S1.OUT_NO
WHERE S2.LOTNO IS NULL

 

COMMIT TRAN
*/
--ROLLBACK TRAN