0

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?

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
Margaret
  • 5,749
  • 20
  • 56
  • 72

3 Answers3

5

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
Vinko Vrsalovic
  • 330,807
  • 53
  • 334
  • 373
  • The join might belong in the update clause, but I've never tried it myself: http://stackoverflow.com/questions/871905/use-select-inside-a-update-query – David Aug 20 '09 at 00:44
  • I know I have tried it, but now it's too late and I'm sleepy :-). I think the syntax's correct using an implied join and your link uses an explicit join. – Vinko Vrsalovic Aug 20 '09 at 00:48
  • Wow. That turned it into a 6 minute query instead of a 20 hour one. :O. ...Now to verify the result sets are the same... – Margaret Aug 20 '09 at 02:26
  • 1
    Add some indexes on Episode in both tables and you might be able to shave it down to under a minute. – JohnFx Aug 20 '09 at 05:08
  • Yes indexes would be due in both tables as well – Vinko Vrsalovic Aug 20 '09 at 08:44
1

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

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

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_3.htm

pjp
  • 17,039
  • 6
  • 33
  • 58