Update table ID from like value in another table with SQL 2008

Question

I have a table where I am capturing referal links. I have another table that contains the host name when a referal link is inserted (Duplicates are not allowed in the host table. So if it already exists the ID is pulled from the referal host table). HEre are the table structures:

Referal Table:
referalTableID
referalLink
referalHostID (FK from the referal host table)

Referal Host Table:
referalHostID
ReferalHostName
ReferalHostDisplayName

There was an error in my applcaiton where the referalHostID was not getting added to the Referal table. So I have to manaully update the referal table with the correct ID. I need to update the Referal Table with teh correct ID from the referal host table. I started with some SQL like this:

;with RHTable as (
Select * from ReferalHostTable)

Update ReferalTable Set referalHostID = (
Select referalHostID from RHTable, ReferalHostTable where ReferalHostTable link '%' + RHTable.ReferalHostName + '%')

This is not working becuase multiple values keep being selected in the sub query. I kind of understand why, but does anyone have a better solution?

Thanks

Answer 1

Should be something similar to this:

Update r
Set r.referalHostID = h.referalHostID
from ReferalTable r
inner join ReferalHostTable h
    on r.referalLink like '%' + h.ReferalHostName + '%'
where r.ReferalHostID is null 
    or r.referalHostID <> h.referalHostID

Answer 2

It is concerning that you are matching hostnames using % on either side. This will give all kinds of false positives. E.g., meta.stackoverflow.com will match stackoverflow.com.