... first digit indicates the major group, second digit the level2 group, 3rd and 4th digits level3 group and next 3 digits individual item code. Adopting the above logic, this is the sql: UPDATE PROD_GRP S LEFT OUTER JOIN ( SELECT COALESCE( A.GROUP3, ...