/* saves having to reset all FK's if diagnoses renamed to _diagnoses */
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE IF NOT EXISTS _diagnoses LIKE diagnoses;
INSERT INTO _diagnoses SELECT * FROM diagnoses;
TRUNCATE diagnoses;
INSERT INTO diagnoses SELECT * FROM lims_test.diagnoses;
SET FOREIGN_KEY_CHECKS = 1;

/* check existing requests, new vs old terms: */
select d1.name as 'new', d2.name as 'old',
	case when d1.name <> d2.name then 'YES' end as diff 
from requests r
	join ( request_report_detail rrd1 join diagnoses d1 on rrd1.diagnosis_id = d1.id )
		on rrd1.request_id = r.id
	join ( request_report_detail rrd2 join _diagnoses d2 on rrd2.diagnosis_id = d2.id )
		on rrd2.request_id = r.id
group by d1.id
order by diff desc, d1.name

/* correct ICDO3 errors: */
update bristol.diagnoses d set d.ICDO3 = NULL
where d.name in (
	'Interim result: review within context of an integrated report',
	'Severe mosquito bite allergy',
	'Recipient cells'
);
update bristol.diagnoses d set d.ICDO3 = NULL
where d.name = ''; /* empty on src db */