1、更新院校国家代码（部标代码）
UPDATE pre_college_teacher SET pre_college_teacher.yxdm = (SELECT yxmc2yxdm_2.yxdm FROM yxmc2yxdm_2 WHERE 1 AND yxmc2yxdm_2.yxmc = pre_college_teacher.yxmc ) WHERE 1 AND pre_college_teacher.yxdm IS NULL
UPDATE pre_college_teacher SET pre_college_teacher.yxdm = (SELECT pre_college_yxmc2yxdm.yxdm FROM pre_college_yxmc2yxdm WHERE 1 AND pre_college_yxmc2yxdm.yxmc = pre_college_teacher.yxmc ) WHERE 1 AND pre_college_teacher.yxdm IS NULL
UPDATE pre_college_teacher_tw
SET pre_college_teacher_tw.yxdm =
(SELECT pre_college_school_tw.yxdm FROM pre_college_school_tw WHERE 1 AND pre_college_school_tw.yxmc = pre_college_teacher_tw.yxmc) WHERE 1 AND pre_college_teacher_tw.yxdm = ''
2、更新本科专业代码
UPDATE pre_college_school_major_intro
SET pre_college_school_major_intro.zydm =
(SELECT pre_chsi_zy_bk.zydm FROM pre_chsi_zy_bk WHERE 1 AND pre_chsi_zy_bk.zymc = pre_college_school_major_intro.zymc) WHERE 1 AND pre_college_school_major_intro.zycc = 1
3、更新专科小类专业代码
UPDATE pre_college_school_major_intro
SET pre_college_school_major_intro.zydm =
(SELECT pre_chsi_zy_zk.zydm FROM pre_chsi_zy_zk WHERE 1 AND pre_chsi_zy_zk.zymc = pre_college_school_major_intro.zymc) WHERE 1 AND pre_college_school_major_intro.zycc = 2
4、更新专科大类专业代码
UPDATE pre_college_school_major_intro
SET pre_college_school_major_intro.zydm =
(SELECT DISTINCT pre_chsi_zy_zk.mldm FROM pre_chsi_zy_zk WHERE 1 AND pre_chsi_zy_zk.mlmc = pre_college_school_major_intro.zymc) WHERE 1 AND pre_college_school_major_intro.zycc = 2 AND pre_college_school_major_intro.zydm IS NULL
5、更新专科专业目录外专业代码
UPDATE pre_college_school_major_intro
SET pre_college_school_major_intro.zydm =
(SELECT DISTINCT pre_edu_zy_zk_out.zydm FROM pre_edu_zy_zk_out WHERE 1 AND pre_edu_zy_zk_out.zymc = pre_college_school_major_intro.zymc) WHERE 1 AND pre_college_school_major_intro.zycc = 2 AND pre_college_school_major_intro.zydm IS NULL
6、删除没有个人简介的记录
DELETE FROM [dbo].[ArticleData_184] WHERE DataObject2 LIKE '<p>内容贡献者</p>%';
7、更新院校隶属代码
UPDATE pre_chsi_college
SET pre_chsi_college.yxlsdm =
(SELECT pre_college_cat.aid FROM pre_college_cat WHERE 1 AND pre_college_cat.type = 'yxls' AND pre_college_cat.title = pre_chsi_college.yxls)
8、教师论文相关
UPDATE pre_college_thesis_new
SET pre_college_thesis_new.status =
(SELECT pre_college_lunwen_get_2.status FROM pre_college_lunwen_get_2 WHERE 1 AND pre_college_lunwen_get_2.tid = pre_college_thesis_new.tid) WHERE 1 AND pre_college_thesis_new.status IS NULL
9、更新点击数
UPDATE pre_college_school
SET pre_college_school.viewnum =
(SELECT baluobo_category_house_thread.viewnum FROM baluobo_category_house_thread WHERE 1 AND baluobo_category_house_thread.subject = pre_college_school.yxmc) WHERE 1 AND pre_college_school.viewnum IS NULL
10、更新校控线表的院校代码
UPDATE baluobo_category_scores_school_30 SET baluobo_category_scores_school_30.school_code = (SELECT yxmc2yxdm_3.yxdm FROM yxmc2yxdm_3 WHERE 1 AND yxmc2yxdm_3.yxmc = baluobo_category_scores_school_30.school_name ) WHERE 1 AND baluobo_category_scores_school_30.xid > 11907
11、更新海外院校库省州ID
UPDATE baluobo_abroad_institutions SET baluobo_abroad_institutions.ProvinceId = (SELECT baluobo_abroad_area.aid FROM baluobo_abroad_area WHERE 1 AND baluobo_abroad_institutions.CnProvince = baluobo_abroad_area.title AND baluobo_abroad_area.cid = 1 AND baluobo_abroad_area.aup = 'us' ) WHERE 1 AND baluobo_abroad_institutions.ProvinceId IS NULL
12、更新youeryuan的地域dm
UPDATE pre_c_youeryuan SET pre_c_youeryuan.djsdm = (SELECT temp_middleschool.aid FROM temp_middleschool WHERE 1 AND temp_middleschool.upid = 33 AND temp_middleschool.title = pre_c_youeryuan.djs ) WHERE 1 AND pre_c_youeryuan.djsdm IS NULL
12、更新youeryuan的xsqdm
UPDATE pre_c_youeryuan
SET pre_c_youeryuan.xsqdm =
(SELECT temp_middleschool.aid FROM temp_middleschool
WHERE 1
AND
temp_middleschool.upid IN ('3501','3502','3503','3504','3505','3506','3507','3508','3509')
AND
pre_c_youeryuan.xsq = temp_middleschool.title
)
WHERE 1
AND pre_c_youeryuan.xsqdm IS NULL
13、专业排名
UPDATE `baluobo_category_major_ranking_1` SET `baluobo_category_major_ranking_1`.catid = (SELECT baluobo_blb_professional2012.xkmldm FROM baluobo_blb_professional2012 WHERE 1 AND baluobo_blb_professional2012.levelid = 1 AND baluobo_blb_professional2012.type = 'cat' AND baluobo_blb_professional2012.xkmlmc = `baluobo_category_major_ranking_1`.catname ) WHERE 1 AND baluobo_category_major_ranking_1.type = 'menlei' //大类 UPDATE `baluobo_category_major_ranking_1` SET `baluobo_category_major_ranking_1`.upcatid = (SELECT baluobo_blb_professional2012.zyldm FROM baluobo_blb_professional2012 WHERE 1 AND baluobo_blb_professional2012.levelid = 1 AND baluobo_blb_professional2012.type = 'class' AND baluobo_blb_professional2012.zylmc = `baluobo_category_major_ranking_1`.upcatname ) WHERE 1 AND baluobo_category_major_ranking_1.type = 'zhuanye' AND baluobo_category_major_ranking_1.upcatid is NULL
14.teacher id update
UPDATE `tw_teacher_102` SET `tw_teacher_102`.tid = (SELECT pre_college_teacher.tid FROM pre_college_teacher WHERE 1 AND `tw_teacher_102`.yxmc = pre_college_teacher.UNIVERSITY AND `tw_teacher_102`.xsmc = pre_college_teacher.DEPARTMENT AND `tw_teacher_102`.xlmc = pre_college_teacher.xuelei AND `tw_teacher_102`.xm = pre_college_teacher.xm ) WHERE 1 AND `tw_teacher_102`.yxdm <> '0025' AND `tw_teacher_102`.yxdm <> '0039' AND `tw_teacher_102`.tid IS NULL