Optimal way to update table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Optimal way to update table

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-31 : 13:01:42

I am using the query below to update a table of about 1 million records. I am actually updating about 20 different columns, but for the sake of simplicity, I am only showing the update query with 2 columns.

The query has been running now for about 4 hours, and I am not sure when it's going to end :(

Any opinions on a more optimal way to update?

UPDATE Table1 t set(t1.name, t1.accountNumber) =
(SELECT name, accountNumber
FROM Table2 t2
WHERE t2.accountNumber = t1.accountNumber);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 13:17:44

[code]UPDATE t1
SET t1.ColA = t2.Col1,
t1.ColB = t2.Col2,
t1.ColP = t2.Col9,
t1.ColZ = t2.Col44
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.AccountNumber = t1.AccountNumber[/code]

Peter Larsson
Helsingborg, Sweden

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-31 : 13:22:25

why is that more optimal than the original way? isn't using WHERE joining the tables also?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 13:46:56

My suggestion is not using subqueries for every record in destination table.

Try it and time it...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources