刷新数据的sql语句

 

步骤

*第一步--》建立临时表

--hyy  FLDEX14-
CREATE TABLE tmp_hy(FLDEX2 VARCHAR2(32),FLDITYPE VARCHAR2(32),FLDEX14 varchar2(32),FLDEX13 VARCHAR2(32),FLDEX17 VARCHAR2(32),
 FLD2GOPENDATE VARCHAR2(32),FLDEX18 VARCHAR2(32),FLDEX19 VARCHAR2(32),FLD3GOPENDATE VARCHAR2(32),FLDEX20 VARCHAR2(32));
-- 楼宇编号  楼宇类别1  对应OA表站号  对应OA表站名  2G室分是否开通  2G室分开通时间 
 --2G覆盖入网情况  3G室分是否开通  3G室分开通时间  3G覆盖入网情况

*第二步--》导入excel 数据、
select * from tmp_hy for update;

*第三步--》翻译字典项
--2G室分是否开通
UPDATE TMP_HY T SET T.FLDEX17 = CASE T.FLDEX17 WHEN '是' THEN '1'  WHEN '否' THEN '2' END ; 
--3G室分是否开通
UPDATE TMP_HY T SET T.FLDEX19 = CASE T.FLDEX19 WHEN '是' THEN '1'  WHEN '否' THEN '2' END ;  
 --2G覆盖入网情况
UPDATE TMP_HY T SET T.FLDEX18 = CASE T.FLDEX18 WHEN '开通' THEN '1'  WHEN'未开通' THEN '2' END ;
--3G覆盖入网情况
UPDATE TMP_HY T SET T.FLDEX20 = CASE T.FLDEX20 WHEN '开通' THEN '1'  WHEN'未开通' THEN '2' END ; 
 --2G室分开通时间
UPDATE TMP_HY T SET T.FLD2GOPENDATE =
CASE T.FLD2GOPENDATE WHEN '2009' THEN '59'  WHEN '2010' THEN '60' WHEN '2011' THEN '61' END ; 
--3G室分开通时间
UPDATE TMP_HY T SET T.FLD3GOPENDATE =
CASE T.FLD3GOPENDATE WHEN '2009' THEN '59'  WHEN '2010' THEN '60' WHEN '2011' THEN '61' END ; 

*第四步--》更新数据
UPDATE TAR_STATION T
   SET (T.FLDEX14,
        T.FLDEX13, 
        T.FLDEX17,
        T.FLD2GOPENDATE,
        T.FLDEX18,
        T.FLDEX19,
        T.FLD3GOPENDATE,
        T.FLDEX20,
        FLDITYPE) =
       (SELECT TRIM(T1.FLDEX14),
               TRIM(T1.FLDEX13),
               T1.FLDEX17,
               T1.FLD2GOPENDATE,
               T1.FLDEX18,
               T1.FLDEX19,
               T1.FLD3GOPENDATE,
               T1.FLDEX20,
               FLDITYPE
          FROM TMP_HY T1
         WHERE T.FLDEX2 = TRIM(T1.FLDEX2) AND ROWNUM<2)
 WHERE EXISTS (SELECT 1 FROM TMP_HY T1 WHERE TRIM(T1.FLDEX2)=T.FLDEX2 );

=====================================================================================
查询语句
# 临时表 
select * from TMP_HY;
#楼宇表
SELECT * FROM TAR_STATION;
#其他数据

select * from tsm_code t WHERE T.FLDITYPEID = '5001' ORDER BY T.FLDDICTORDER ASC; 

select * from tmp_code;

select * from tsm_entity_config;

delete from tmp_hy;