##10800 — Assmt_Update_Therapy1.Item_ID ← pPathway.Item_ID
##10797 follow-up: Update #lookupDb#.Assmt_Update_Therapy1.Item_ID
to the matching pPathway.Item_ID for the same Description
(Assmt_Update_Therapy1.Problem = pPathway.Description) and same Pathway_ID.
That pathway row holds the Incrmt1 therapy update needs.
UPDATE #lookupDb#.Assmt_Update_Therapy1 a
INNER JOIN (
SELECT Description, Pathway_ID, MAX(Item_ID) AS Item_ID, MAX(Incrmt1) AS Incrmt1
FROM #lookupDb#.pPathway
WHERE TRIM(IFNULL(Description,'')) <> ''
GROUP BY Description, Pathway_ID
) p ON p.Description = a.Problem AND p.Pathway_ID = a.Pathway_ID
SET a.Item_ID = p.Item_ID
WHERE IFNULL(a.Item_ID, 0) <> IFNULL(p.Item_ID, 0);
SELECT a.ID,
a.Item_ID AS old_Item_ID,
p.Item_ID AS new_Item_ID,
a.Problem,
a.Pathway_ID,
p.Incrmt1,
a.Source,
a.`Update` AS Update_Reason
FROM #lookupDb#.Assmt_Update_Therapy1 a
INNER JOIN (
SELECT Description, Pathway_ID,
MAX(Item_ID) AS Item_ID,
MAX(CASE WHEN Incrmt1 IS NOT NULL AND Incrmt1 <> '' AND Incrmt1 > 0 THEN Incrmt1 ELSE NULL END) AS Incrmt1
FROM #lookupDb#.pPathway
WHERE TRIM(IFNULL(Description, '')) <> ''
GROUP BY Description, Pathway_ID
) p ON p.Description = a.Problem
AND p.Pathway_ID = a.Pathway_ID
WHERE IFNULL(a.Item_ID, 0) <> IFNULL(p.Item_ID, 0)
ORDER BY a.Problem, a.ID
SELECT COUNT(*) AS n
FROM #lookupDb#.Assmt_Update_Therapy1 a
INNER JOIN #lookupDb#.pPathway p
ON p.Description = a.Problem
AND p.Pathway_ID = a.Pathway_ID
AND IFNULL(a.Item_ID, 0) = IFNULL(p.Item_ID, 0)
SELECT a.ID, a.Item_ID, a.Problem, a.Pathway_ID, a.Source
FROM #lookupDb#.Assmt_Update_Therapy1 a
WHERE TRIM(IFNULL(a.Problem, '')) <> ''
AND NOT EXISTS (
SELECT 1 FROM #lookupDb#.pPathway p
WHERE p.Description = a.Problem
AND p.Pathway_ID = a.Pathway_ID
)
AND (a.Problem LIKE '%GG%' OR a.Problem LIKE '%Sit to Stand%' OR a.Problem LIKE '%Car Transfer%')
ORDER BY a.Problem, a.ID
LIMIT 50
Already matching Item_ID (same Problem + Pathway_ID): #Val(qMatch.n)#
Rows to update: #qMismatch.recordCount#
GG/Sit/Car orphans (no pPathway on Pathway_ID): #qOrphans.recordCount# (sample ≤50)
Preview — Item_ID changes (first 100)
| ID | Old Item_ID | New Item_ID | Pathway_ID |
Incrmt1 | Problem | Source |
| #ID# |
#old_Item_ID# |
#new_Item_ID# |
#Pathway_ID# |
#Incrmt1# |
#HTMLEditFormat(Problem)# |
#HTMLEditFormat(Source)# |
Nothing to update — all joinable Assmt_Update_Therapy1 rows already match pPathway.Item_ID.
Orphans (not updated): these Assmt rows have no pPathway with the same Problem + Pathway_ID.
Fix pathway data separately if needed.
| ID | Item_ID | Pathway_ID | Problem |
|---|
| #ID# | #Item_ID# | #Pathway_ID# |
#HTMLEditFormat(Problem)# |
No rows updated.
UPDATE #lookupDb#.Assmt_Update_Therapy1 a
INNER JOIN (
SELECT Description, Pathway_ID, MAX(Item_ID) AS Item_ID
FROM #lookupDb#.pPathway
WHERE TRIM(IFNULL(Description, '')) <> ''
GROUP BY Description, Pathway_ID
) p ON p.Description = a.Problem
AND p.Pathway_ID = a.Pathway_ID
SET a.Item_ID = p.Item_ID
WHERE IFNULL(a.Item_ID, 0) <> IFNULL(p.Item_ID, 0)
Updated #Val(updResult.recordCount)# Assmt_Update_Therapy1 row(s).
Re-run therapy auto-careplan / Recert so Sit to Stand and Car Transfer pick up Incrmt1 via the corrected Item_ID.
#HTMLEditFormat(cfcatch.message)# — #HTMLEditFormat(cfcatch.detail)#
← Migrations index