update spn_prod.staff sps
        left join
    spn_staff.staff sss ON sps.login_id = concat('N',
            if(sss.staff < 10,
                LPAD(sss.staff, 2, '0'),
                sss.staff))
        left join
    spn_prod.hr_staff sphs ON sps.login_id = sphs.staff_code
        left join
    spn_prod.users spu ON sps.login_id = spu.login_id 
		left join 
	spn_prod.staff_spouse spssp on spssp.staff_id = sps.login_id
set 
    sps.surname = sss.S_NAME,
    sps.firstname = sss.O_NAME,
    spu.english_name = concat(sss.S_NAME, ' ', sss.O_NAME),
    spu.chinese_name = sss.CCNAME,
    sps.marital_status = if(sss.STATUS = 'Marriage', 2, 1),
    sps.is_full_time = if(sss.PARTTIME = 'Full Time', 1, 0),
	sps.responsibility = sss.JOB,
    sps.tel = sss.TEL_1,
    spu.mobile = sss.TEL_2,
    spu.email = sss.EMAIL,
    sps.first_aids_date = sss.FIRSA_DATE,
    sps.first_aids_no = sss.FIRSA_NO,
    sps.kg_no = sss.KG_NO,
    sps.kg_date = sss.KG_DATE,
    sphs.join_date = sss.SDATE,
    sps.seniority_before_entry = sss.PRE_WORKING_YEAR,
    sps.address = concat(sss.ADDRES1, ' ', sss.ADDRES2),
	sps.ccc_no = sss.CCC_NO,
	sps.ccc_date = sss.CCC_DATE,
	sps.educational = sss.EDUCATION,
	sps.nationality = sss.NATIONALIT,
	sps.profession = '',
	sps.ten_years = sss.TEN_YEAR,
	sps.fifteen_years = sss.FIFTY_YEAR,
	sps.retirement_date = sss.RET_DATE,
	sps.business_trip = sss.TRIP,
	sps.business_trip_date = sss.TRIPDATE,
	sps.business_trip_back_date = sss.RETURN_D,
    sps.religion = sss.RELIGION,
	sps.file_no = sss.FILE_NO,
	spssp.name = sss.SPOUSE_NA1,
	spssp.tel = sss.SPOUSE_TEL,
	spssp.hkid = sss.SPOID,
	spssp.mother_tel = sss.TEL_MOTHER
where
    sss.STAFF is not null;

update spn_prod.staff sps
        left join
    spn_prod.hr_staff sphs ON sps.login_id = sphs.staff_code
        left join
    spn_prod.users spu ON sps.login_id = spu.login_id,
    spn_staff.staff sss 
set 
    sps.surname = sss.S_NAME,
    sps.firstname = sss.O_NAME,
    spu.english_name = concat(sss.S_NAME, ' ', sss.O_NAME),
    spu.chinese_name = sss.CCNAME,
    sps.marital_status = if(sss.status = 'Marriage', 2, 1),
    sps.is_full_time = if(sss.parttime = 'Full Time', 1, 0),
    sps.tel = sss.tel_1,
    spu.mobile = sss.tel_2,
    spu.email = sss.email,
    sps.first_aids_date = sss.firsa_date,
    sps.first_aids_no = sss.firsa_no,
    sps.kg_no = sss.KG_NO,
    sps.kg_date = sss.KG_DATE,
    sphs.join_date = sss.SDATE,
    sps.seniority_before_entry = sss.PRE_WORKING_YEAR,
    sps.address = concat(sss.addres1, ' ', sss.addres2),
    sps.religion = sss.RELIGION
where
    sss.STAFF = 4 and sps.login_id = 'N403';