純粹紀錄
有時候這種比較複雜的會一時忘記怎麼寫
直接把語法存下來以便以後查找
UPDATE A
SET
off_no = B.OFF_NO,
unit_id = B.UNIT_ID,
parent_id = B.DIRECT_UNIT_FID,
direct_off_no = C.DIRECT_OFF_NO,
org_name = B.UNIT_FNAME,
unit_name = B.UNIT_NAME,
unit_level = B.UNIT_LEVEL,
off_level = CASE WHEN C.OFF_LEVEL = '1' AND B.UNIT_LEVEL = '0' THEN '0'
WHEN C.OFF_LEVEL = '3' THEN '2' END,
del_flg = 0,
mdf_date = GETDATE(),
mdf_user = 'ReceivePersonalInfo'
FROM TableA AS A
INNER JOIN TableB AS B
ON B.UNIT_FID = A.org_id
LEFT JOIN TableC AS C
ON C.OFF_NO = B.OFF_NO
IF @@Error <> 0 BEGIN SET @chk = 1 END
INSERT INTO TableA(org_id,off_no,unit_id,parent_id,direct_off_no,
org_name,unit_name,unit_level,off_level,del_flg,
crt_date,crt_user,mdf_date,mdf_user)
SELECT TableB.UNIT_FID,TableB.OFF_NO,TableB.UNIT_ID,TableB.DIRECT_UNIT_FID,TableC.DIRECT_OFF_NO,
TableB.UNIT_FNAME,TableB.UNIT_NAME,TableB.UNIT_LEVEL,CASE WHEN TableC.OFF_LEVEL = '1' AND T_UNIT.UNIT_LEVEL = '0' THEN '0'
WHEN TableC.OFF_LEVEL = '1' AND T_UNIT.UNIT_LEVEL <> '0' THEN '3'
WHEN TableC.OFF_LEVEL = '2' AND T_UNIT.UNIT_LEVEL = '0' THEN '1'
WHEN TableC.OFF_LEVEL = '2' AND T_UNIT.UNIT_LEVEL <> '0' THEN '3'
WHEN TableC.OFF_LEVEL = '3' THEN '2' END,'0',
GETDATE(),'ReceivePersonalInfo',GETDATE(),'ReceivePersonalInfo'
FROM TableB INNER JOIN TableC ON TableB.OFF_NO = TableC.OFF_NO
WHERE TableB.UNIT_FID NOT IN(SELECT org_id FROM EMP_ORG)
IF @@Error <> 0 BEGIN SET @chk = 1 END