I have a script which iterates through a table and updates a specific column with a temp table generated a moment beforehand. However I was expecting it to only update the 20 or so rows in this case, and it ended up running for close to 3 hours before I killed it.
Below is an example of the code. To the best of my knowledge the issue is that the Update statement is not referencing the alias in the from clause. This causes the update statement to try and run through the entire table although it only runs an actual update when it comes across one of the connected values.
Update MasterTable
set FieldtoUpdate = b.TempFieldValue
from MasterTable a
join TempTable b on A.Pkey = b.Pkey
user27360043 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
You could try adding:
where MasterTable.Pkey IN (select TempTable.Pkey)
Even better: make the join on a subquery (so first, subquery Mastertable with IN statement above and then join that result to the TempTable). Joining large sets is expensive.