I just needed to find a good link that explains MERGE to enhance my grasp of it and then look at your query again. For the benefit of others, here's the query with portal 1103 used on the first INNER JOIN. *NOT* to be used on the production case, of course but to illustrate what's going on. The page (tab) names are right and when looking at portal 0 those tab names are also right.
My query looked at the incorrect content names based on a JOIN with TabId values. The other way below shows the correct Tab Names that will be used to update / Insert into the the Metadata table by getting the right names from the Tabs table.
This answers my own question. Just had to dig a bit into how the correct query works and hopefully this benefits others.
MERGE INTO dbo.[ContentItems_MetaData] M
FROM dbo.[ContentItems] C
JOIN dbo.[Tabs] T ON C.ContentItemID = T.ContentItemID
AND T.PORTALID = 1103 (and also use 0 so one can see what's happening with this JOIN)
S ON (S.ContentItemID = M.ContentItemID AND M.MetaDataID = 1)
WHEN MATCHED AND IsNull(M.MetaDataValue,'') != S.TabName THEN UPDATE SET MetaDataValue = S.TabName
WHEN NOT MATCHED THEN INSERT
( ContentItemID, MetaDataID, MetaDataValue)
VALUES (S.ContentItemID, 1, TabName);