syncDataToKalolsavam.sql

# Sync Data From sasthrolsavamDB to KalolsavamDB
# ==============================================

# Insert school details from another db

INSERT INTO `kalolsavam_2013`.`school_details`
    (`school_code`, `class_start`, `class_end`, `school_phone`, `school_email`,
    `hm_name`, `hm_phone`, `principal_name`, `principal_phone`,
    `teachers`, `strength_lp`, `strength_up`, `strength_hs`,
    `strength_hss`, `strength_vhss`, `total_strength`,
    `is_create_report`, `is_finalize`)

    SELECT `school_code`, `class_start`, `class_end`, `school_phone`, `school_email`,
    `hm_name`, `hm_phone`, `principal_name`, `principal_phone`,
    `teachers`, `strength_lp`, `strength_up`, `strength_hs`,
    `strength_hss`, `strength_vhss`, `total_strength`,
    `is_create_report`, `is_finalize`
    FROM `sasthrolsavam_2013`.`school_details`
    WHERE school_code NOT IN
        ( SELECT school_code
        FROM kalolsavam_2013.school_master);

# ============================================================================================

# insert school from another db

INSERT INTO `kalolsavam_2013`.`school_master`
    (`school_code`, `sub_district_code`, `edu_district_code`,
    `rev_district_code`, `school_name`, `school_type`,  `school_status`)

    SELECT `school_code`, `sub_district_code`, `edu_district_code`,
    `rev_district_code`, `school_name`, `school_type`, `school_status`
    FROM sasthrolsavam_2013.school_master
    WHERE school_code NOT IN
        (SELECT school_code
        FROM kalolsavam_2013.school_master);

# =====================================================

# delete invalid rows from school_details

DELETE FROM school_details where school_code NOT IN(select school_code from school_master);

# =====================================================

# delete the school which does not present in remote db

DELETE FROM school_master where school_code NOT IN (select school_code from sasthrolsavam_2013.school_master);
DELETE FROM school_details where school_code NOT IN (select school_code from sasthrolsavam_2013.school_master);

# =====================================================

# updating details from another db
# !!! it need to be run for each column, not working for multiple columns at once

UPDATE school_master
SET
    school_name = ( SELECT school_name
    FROM sasthrolsavam_2013.school_master
    WHERE sasthrolsavam_2013.school_master.school_code=kalolsavam_2013.school_master.school_code);
# =========================
UPDATE school_master
SET
    school_type = ( SELECT school_type
    FROM sasthrolsavam_2013.school_master
    WHERE sasthrolsavam_2013.school_master.school_code=kalolsavam_2013.school_master.school_code);
# =========================
UPDATE school_master
SET
    sub_district_code = ( SELECT sub_district_code
    FROM sasthrolsavam_2013.school_master
    WHERE sasthrolsavam_2013.school_master.school_code=kalolsavam_2013.school_master.school_code);
# =========================
UPDATE school_master
SET
    edu_district_code = ( SELECT edu_district_code
    FROM sasthrolsavam_2013.school_master
    WHERE sasthrolsavam_2013.school_master.school_code=kalolsavam_2013.school_master.school_code);