I am struggling approaching the following problem.

I have two tables

1) contains Top level Site URL
Sites (Site_Id, URL)

2) Contains a trawl of all the top level sites URLS
Trawl (Id, Site_Id, URL)

I want to populate the Trawl.Site_Id which is currently blank with its parent site in the table above. I have been trying to do this but have the following problem.

www.mywebsite.com/community (Top level site)
www.mywebsite.com/community/travel (top level site)
www.mywebsite.com/community/food (Top level site)

When I am update through using:

Upate trawl t, sites s
set t.URl = s.URl
where t.URL like concat(s.Url, "%")

if www.mywebsite.com/community was the last site to be used it would set all the similar Sites URLs to its Id.

Do i need to use a While loop, if so please could you point me the right direction.

Thank you