Recovering mobile number using parent_name and NID

-- get the count of valid formatted mobile numbers against a parent name and national id
SELECT
    t.parent_name_unicode,
    t.nationalidoriginal,
    COUNT(DISTINCT t.mobile_number) validMobileCount,
    GROUP_CONCAT(DISTINCT t.mobile_number
        SEPARATOR ',  ')
FROM
    `stipend_info_latest_experiment` t
WHERE
    ((SUBSTR(t.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(t.`mobile_number`) = 11)
        OR (SUBSTR(t.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(t.`mobile_number`) = 10))
        AND t.parent_name_unicode is not NULL
        AND t.nationalidoriginal is not NULL
        AND lower(t.nationalidoriginal) != lower('NULL')
GROUP BY t.parent_name_unicode , t.nationalidoriginal
ORDER BY validMobileCount DESC;

-- if for same parent name and nId if there is more than one mobile number then view the wrong and correct
-- there may be more than one proper formatted mobile number found. that's a problem. we will do if only one valid mobile number is found
SELECT
    f.parent_name_unicode,
    f.nationalidoriginal,
    f.mobile_number,
    s.mobile_number
FROM
    `stipend_info_latest_experiment` f
        INNER JOIN
    `stipend_info_latest_experiment` s ON f.parent_name_unicode = s.parent_name_unicode
        AND f.nationalidoriginal = s.nationalidoriginal
        INNER JOIN
    (SELECT
        COUNT(DISTINCT t.mobile_number) validMobileCount,
            t.mobile_number
    FROM
        `stipend_info_latest_experiment` t
    WHERE
        ((SUBSTR(t.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
            AND LENGTH(t.`mobile_number`) = 11)
            OR (SUBSTR(t.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
            AND LENGTH(t.`mobile_number`) = 10))
            AND t.mobile_flag = 0 AND t.amount_flag = 0 AND t.flag = 0
    GROUP BY t.parent_name_unicode , t.nationalidoriginal
    having validMobileCount = 1) t
    ON f.mobile_number = t.mobile_number
WHERE
    f.mobile_number != s.mobile_number
        AND f.nationalidoriginal != 'Null'
        AND ((SUBSTR(f.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(f.`mobile_number`) = 11)
        OR (SUBSTR(f.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(f.`mobile_number`) = 10))
        AND NOT ((SUBSTR(s.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(s.`mobile_number`) = 11)
        OR (SUBSTR(s.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(s.`mobile_number`) = 10));

-- if same (parent name, nId) have different mobile name and only one of them is in correct format replace the wrong ones with the correct
UPDATE
    `stipend_info_latest_experiment` f
        INNER JOIN
    `stipend_info_latest_experiment` s ON f.parent_name_unicode = s.parent_name_unicode
        AND f.nationalidoriginal = s.nationalidoriginal
        INNER JOIN
    (SELECT
        COUNT(DISTINCT t.mobile_number) validMobileCount,
            t.mobile_number
    FROM
        `stipend_info_latest_experiment` t
    WHERE
        ((SUBSTR(t.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
            AND LENGTH(t.`mobile_number`) = 11)
            OR (SUBSTR(t.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
            AND LENGTH(t.`mobile_number`) = 10))
    GROUP BY t.parent_name_unicode , t.nationalidoriginal
    having validMobileCount = 1) t
    ON f.mobile_number = t.mobile_number
    set s.mobile_number = f.mobile_number
WHERE
    f.mobile_number != s.mobile_number
        AND f.nationalidoriginal != 'Null'
        AND
        ((SUBSTR(f.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(f.`mobile_number`) = 11)
        OR (SUBSTR(f.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(f.`mobile_number`) = 10))
        AND NOT
        ((SUBSTR(s.`mobile_number`, 1, 3) IN ('017' , '016', '015', '019', '018', '011')
        AND LENGTH(s.`mobile_number`) = 11)
        OR (SUBSTR(s.`mobile_number`, 1, 2) IN ('17' , '16', '15', '19', '18', '11')
        AND LENGTH(s.`mobile_number`) = 10));