Update one table from another using a join

June 2, 2009 · No CommentsComments Feed

I can never remember the exact syntax for the update join. I don't use it too much, but it is really handy. So, if I put it in my blog, I will be able to quickly lay hands on it next time I need it.

update i set i.primaryAliasID= c.aliasID<br />from content i, content_alias c<br />where i.contentid = c.contentid UPDATE [dbo].[content]

<br />SET primaryAliasID=(SELECT top 1 aliasID<br />FROM [dbo].[content_alias]<br />where contentid =content.contentid)

Both of the below queries do the same thing, the top one took 4.5 seconds the lower one took 2 mins.