1.问题描述:对照a表,根据id将name对照着更新b表
2.思路:创建临时表存对照的数据,比如根据id更新name,那么这个临时表存id和name;使用更新语句更新符合条件的数据
3.MSSQL写法:
--创建临时表
SELECT A.FDEPTID,B.FNAME INTO #T FROM T_BD_DEPARTMENT A INNER JOIN T_BD_DEPARTMENT_L B ON A.FDEPTID = B.FDEPTID
--只更新部门维度且设置了部门ID的数据
UPDATE KDY_FA_DEPRECIATION SET FDPPROJECTNAME = #T.FNAME FROM #T,KDY_FA_DEPRECIATION WHERE #T.FDEPTID = KDY_FA_DEPRECIATION.FDPPROJECT
AND FDPDIMENSION = ‘BD_Department‘ AND FDPPROJECT > 0
4.ORACLE写法:
--创建会话级临时表存部门对照数据
CREATE GLOBAL Temporary TABLE DEMOTEST
ON COMMIT PRESERVE ROWS
AS
SELECT A.FDEPTID,B.FNAME FROM T_BD_DEPARTMENT A INNER JOIN T_BD_DEPARTMENT_L B ON A.FDEPTID = B.FDEPTID
--只更新部门维度且设置了部门ID的数据
--SELECT FDPPROJECTNAME,FDPPROJECT FROM KDY_FA_DEPRECIATION WHERE FDPDIMENSION = ‘BD_Department‘ AND FDPPROJECT > 0
UPDATE KDY_FA_DEPRECIATION S SET S.FDPPROJECTNAME = (SELECT B.FNAME FROM DEMOTEST B WHERE S.FDPPROJECT = B.FDEPTID)
WHERE S.FDPDIMENSION = ‘BD_Department‘ AND S.FDPPROJECT > 0