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.
| Author |
Topic |
|
tempp
Starting Member
2 Posts |
 Posted - 2006-12-24 : 07:20:55
|
hey yall :)a quick but very important (performance wise) question:i need to update a few thousands of records in a table, all with the same value - which is the average of a a few other thousands. now this goes on something like this:update my_table t1set (t1.my_field) =( select avg(t2.my_field) from my_table t2 where // (some simple criteria and join here) t2.id = t1.id AND t2.my_date > <<some date>> - <<some number of days>> AND t2.my_date <= <<some date>>)wheret1.my_date > <<some date>> now the value being updated is the same for all those with the same t1.id whose dates are future to <<some date>>.any suggestions of improving performance of this query?thanx, r. :)Smart bombs exist only in the minds of stupid people. |
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2006-12-24 : 18:21:07
|
| select avg(t2.my_field) as Avg_my_field , t2.id into #temp from my_table t2 where AND t2.my_date > <<some date>> - <<some number of days>> AND t2.my_date <= <<some date>>update t1set (t1.my_field) = t2.Avg_my_fieldfrom my_table t1join #temp t2 on t2.id = t1.idwheret1.my_date > <<some date>>MohammedU |
 |
|
|
tempp
Starting Member
2 Posts |
Posted - 2006-12-24 : 19:43:33
|
Thanx Mohammed, I'll look it up!  Eid Mubarak, Merry Christmas~~~~Smart bombs exist only in the minds of stupid people. |
|
|
|
|
|
|
better performance for an update procedure?