SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE '%agency_1150%' GROUP BY TABLE_SCHEMA

Duplicate Assessments (Same Admit_ID + F26 + F27)

SET innodb_strict_mode = 0; SELECT a.Assmt_ID, a.Admit_ID, a.F26, a.F27, p.Pt_First, p.Pt_Last, COUNT(*) AS tot_count FROM #Request.prefix_db_agency#.pAssessments a INNER JOIN #Request.prefix_db_agency#.pPatients p ON p.Patient_ID = a.Patient_ID WHERE a.Status = 0 AND p.Status IN (0,1,4) GROUP BY a.Assmt_ID, a.Admit_ID, a.F26, a.F27, p.Pt_First, p.Pt_Last HAVING COUNT(*) > 1 ORDER BY a.Admit_ID, p.Pt_Last, p.Pt_First #ValueList(getdups.Assmt_ID)#

#Request.prefix_db_agency#

Patient Name	Assmt_ID	Admit_ID	F26	F27	Total Count	Agency Database
#Pt_Last#, #Pt_First#	#Assmt_ID#	#Admit_ID#	#F26#	#F27#	#tot_count#	#Request.prefix_db_agency#

UPDATE #Request.prefix_db_agency#.pAssessments t1 INNER JOIN #Request.prefix_db_agency#.pAssessments t2 ON t1.Admit_ID = t2.Admit_ID AND t1.F26 = t2.F26 AND t1.F27 = t2.F27 AND t1.Status = 0 AND t2.Status = 0 AND t1.AutoAssmt_ID < t2.AutoAssmt_ID SET t1.Status = 2 WHERE t1.Admit_ID = AND t1.F26 = AND t1.F27 =

Done — #totalUpdated# duplicate row(s) set to Status=2 for #Request.prefix_db_agency#. Highest AutoAssmt_ID kept active.

Resolve Duplicates for #Request.prefix_db_agency#