更新多个表的不同字段到一个表中

2017-01-12 19:05:48来源:CSDN作者:zhuwei_clark人点击

update eval_infraction ei#关联车,证,企业信息LEFT JOIN (        SELECT ci.VEH_STATUS,ci.VEH_TYPE_ONE,ci.VEH_TYPE_TWO,ci.TOTAL_KG,ci.PLATE_BRAND,ci.ENGINE_NUM,                     ci.VEH_FRAMENUM,ci.VEH_COLOR,ci.ADDRESS,ci.VEHICLE_MASTER,ci.VEH_PLATE_NUM,ci.ENT_NAME,ci.ENT_CODE,                     c.EXAM_DATE,c.TRADING_CARD,c.ENT_DLYSJYXKZ,c.BUS_SCOPE,e.address AS mesAddress,e.representative        FROM car_vehicle_info ci        LEFT JOIN car_vehicle_card c ON ci.ID = c.INFO_ID AND c.state = 1        LEFT JOIN ent_enterprise_info e ON ci.ENT_CODE = e.organ_code) cviON ei.vehicle_merge_plate = cvi.VEH_PLATE_NUM#关联人员信息LEFT JOIN  wks_worker wwON ei.worker_papers_no = ww.papers_no#关联执法人员信息LEFT JOIN (        SELECT e.administratorId,m.departmentId,e.administratorName,u.username,m.departmentName,e.plateNo        FROM eval_marshalling_plate e        LEFT JOIN         (            SELECT em.id,em.departmentId,ud.`name` AS departmentName            FROM            eval_marshalling em            LEFT JOIN u_department ud ON em.departmentId = ud.id        )m ON e.marshallingId = m.id        LEFT JOIN u_user u ON e.administratorId = u.id        WHERE e.administratorId IS NOT NULL        GROUP BY e.plateNo)empON ei.vehicle_merge_plate = emp.plateNoSET         #车辆信息替换        ei.veh_status = cvi.VEH_STATUS,             ei.vehicle_typeone = cvi.VEH_TYPE_ONE,        ei.vehicle_type = cvi.VEH_TYPE_TWO,        ei.tonseat_num = cvi.TOTAL_KG,        ei.plate_brand = cvi.PLATE_BRAND,        ei.engine_num = cvi.ENGINE_NUM,        ei.veh_framenum = cvi.VEH_FRAMENUM,        ei.veh_color = cvi.VEH_COLOR,        ei.vehicle_master = cvi.VEHICLE_MASTER,        ei.mas_address = cvi.ADDRESS,        #人员信息替换        ei.worker_id_card = ww.id_card,        ei.worker_tel = ww.tel,        ei.worker_address = ww.reside_address,        ei.worker_sex = ww.sex,        ei.worker_grade = ww.star_level,        #证信息        ei.ent_jyxkztime = cvi.EXAM_DATE,        ei.mas_xkzpre = SUBSTRING(cvi.TRADING_CARD,1,1),        ei.mas_xkz = SUBSTRING(cvi.TRADING_CARD,3),        ei.ent_jyxkzpre = SUBSTRING(cvi.ENT_DLYSJYXKZ,1,1),        ei.ent_dlysjyxkz = SUBSTRING(cvi.ENT_DLYSJYXKZ,3),        ei.ent_busscope = cvi.BUS_SCOPE,        #企业信息        ei.ent_code = cvi.ENT_CODE,        ei.ent_name = cvi.ENT_NAME,        ei.mas_address = cvi.mesAddress,        ei.ent_corporate = cvi.representative,        #执法人员        ei.zf_id = emp.administratorId,        ei.zf_dept_id = emp.departmentId,        ei.zf_dept = emp.departmentName,        ei.zf_person = emp.administratorName,        ei.zf_username = emp.username,        #修改空值状态        ei.`status` = '未立案'WHERE 1=1AND (ei.`status` is NULL OR ei.`status` = '未立案' OR ei.`status` = '')AND ei.infraction_time> '2017-01-01'

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台