Strange alias tracking problem, some not tracking even though it's there, query to correct

Question

See image above. See how there are two links, but the link appears 4 times once with a LinkName (alias), once without. Now the link did appear in the email twice, once on an image, and once on text, but both times it had an alias, yet the alias did not track on one of them. Is it because the alias was exactly the same on both links? I have submitted a case to marketing cloud support about this, but in the meantime, I'm trying to find a way to remap the correct alias.

Is there a way with SQL to look for LinkName fields that are blank, then look at the left column to get the url, then search that same column for the same url, then fill in the LinkName column with the alias from the url that has it. (Hope that makes sense) I've provided a diagram below to help.

Answer 1

This is what I needed

SELECT n.LinkContent,
CASE WHEN LEN(n.LinkName)>0 THEN
    n.LinkName
ELSE
    n2.LinkName
END as LinkName
FROM [Newsletter Link Ranking Past month] n with (NOLOCK)
JOIN (
    SELECT LinkContent, LinkName
    FROM [Newsletter Link Ranking Past month] with (NOLOCK)
    WHERE LinkName IS NOT NULL
) n2 
ON n.LinkContent = n2.LinkContent