SELECT	master_dept, department_name, count(*)
FROM		pathology_master
GROUP BY	master_dept, department_name
order by master_dept, department_name

TRUNCATE table lab_sub_department;
TRUNCATE table lab_department;

Insert into lab_sub_department
(dept_id, sub_department_name, dept_display_text)
Select  Case 
		when master_dept='PATHOLOGY' then 1 
		when master_dept='RADIOLOGY' then 2 
		when master_dept='NEUROLOGY' then 3 
		else 4 
	end,
department_name, department_name
From pathology_master
Where department_name is not null
GROUP BY	master_dept, department_name


Update 	lab_analyte_master lab, 
										(
										Select 	distinct a.analyteid analyteid, b.id dept_id, c.id sub_dept_id
										from 		pathology_master a, lab_department b, lab_sub_department c
										where 	b.id = c.dept_id
										and 		a.master_dept = b.department_name
										and 		a.department_name = c.sub_department_name
										) dept
SET			department_id = dept.dept_id,
				sub_department_id = dept.sub_dept_id
Where 	lab.analyteid = dept.analyteid



Select 	distinct a.analyteid, a.master_dept, b.id dept_id, c.id sub_dept_id
from 		pathology_master a, lab_department b, lab_sub_department c
where 	b.id = c.dept_id
and 		a.master_dept = b.department_name
and 		a.department_name = c.sub_department_name
