Speed up a UPDATE with SELECT query

Question

I have two tables:

Table 1 has Episode and Code, with Episode as distinct. Table 2 has Episode and Code, but Episode is not distinct (other fields in the table, not relevant to the task, make each row unique).

I want to copy Table 1's Code across to Table 2 for each episode. The current code to do this is as follows:

UPDATE Table2
SET Table2.Code = (SELECT TOP 1 Code FROM Table1 WHERE Episode = Table2.Episode)

This takes hours and hours. (I don't know precisely how many hours, because I cancelled it at about the 20 hour mark.) They are big tables, but surely there's a faster way?

Answer 1

I don't have a SQL Server handy and I'm not completely sure, but I seem to recall there was a syntax like the following which should probably speed things up.

 UPDATE Table2 SET Table2.Code = Table1.Code FROM Table1 
 WHERE Table1.Episode = Table2.Episode

Answer 2

Are there any indices on the "Code" and "Episode" columns on both tables? Those would definitely help speed up things quite a bit!

Marc

Answer 3

You can use UPDATE with joins like this. Note that you have to specify FROM.

UPDATE MyTable
SET MyColVal = O.NewVal
FROM MyTable T
INNER JOIN MyOtherTable O ON T.Id=O.Id
WHERE ...

http://doc.ddart.net/mssql/sql70/ua-uz%5F3.htm