[SQL用法] 多表Update , Insert另一張表 Sql Command

純粹紀錄

有時候這種比較複雜的會一時忘記怎麼寫

直接把語法存下來以便以後查找

	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