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

Duplicate active assessments (same Assmt_ID, Status = 0)

UPDATE mode — older duplicates will be set to Status = 2.

Preview only. Append ?doUpdate=1 to apply.

SELECT Assmt_ID, COUNT(*) AS tot_count, MAX(AutoAssmt_ID) AS keep_AutoAssmt_ID FROM #Request.prefix_db_agency#.pAssessments WHERE Status = 0 GROUP BY Assmt_ID HAVING COUNT(*) > 1 ORDER BY tot_count DESC, Assmt_ID

#Request.prefix_db_agency# — #getdups.recordcount# Assmt_ID(s) with duplicates

Assmt_ID	Active count	Keep AutoAssmt_ID	Agency
#Assmt_ID#	#tot_count#	#keep_AutoAssmt_ID#	#Request.prefix_db_agency#

UPDATE #Request.prefix_db_agency#.pAssessments SET Status = 2, Record_Mod_Date = , Record_Mod_By = WHERE Assmt_ID = AND Status = 0 AND AutoAssmt_ID <

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