Update inner join query efficiencies I have two tables, both with ~30,000 rows.  Anywhere where b.comments is not null, I want to overwrite a.comments with b.comments' data.  Seems simple enough - but my query has been running for 45 minutes and I don't think it's working.  There about 3,000 values in b that are not null - so a max of 3,000 updates.

I know that syntactically this is fine, but I'm wondering if there is a different way I should be writing this query as I cannot seem to get it to run unless I also add in something like ...and batch_id = 12345 to get the number of records down to 1.

UPDATE temp3 a
INNER JOIN temp2 b
ON a.batch_id = b.batch_id 
and a.batch_li = b.batch_li
SET 
a.comments = b.comments
WHERE
b.comments is not null;
(Taken from comment)

Try putting a nonclustered index on both - use batch_id and batch_li as the keys, and make sure to include comments. The order in which you include them depends on the contents of the columns - I'd read up on that before you make a decision.