/* 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 lims_test.diagnoses d2 on rrd2.diagnosis_id = d2.id )
		on rrd2.request_id = r.id
group by d1.id
order by diff desc, d1.name

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