0

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;
Brian Powell
  • 161
  • 1
  • 6

2 Answers2

1

(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.

Cowthulhu
  • 277
  • 1
  • 8
1

The following link is going to explain to you about the cross table update:

How to update 10 million+ rows in MySQL single table as Fast as possible?

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
arvind
  • 11
  • 1