CODEWITHSUNDEEP
mysql
Nitesh
Nitesh

Reputation: 575

Updating a table from another table in MYSQL

I have a temp table which I want to use to update my other table. Below is the code 

UPDATE custompricingtest t1, custompricingtesttemp t2 
SET t1.customerName = t2.customerName
WHERE t1.partnumberSKU = t2.partnumberSKU

Both table has around 14k records, it takes around 10 mins to run this query. Am I doing anything wrong ?

Upvotes: 0

Views: 65

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270767

You may want to create an index on each:

CREATE INDEX idx_sku1 ON custompricingtest (partnumberSKU);
CREATE INDEX idx_sku2 ON custompricingtesttemp (partnumberSKU);

Upvotes: 1

ajreal
ajreal

Reputation: 47331

10 minutes is taking too long time, how about this ?

UPDATE custompricingtest
SET t1.customerName = 
(select custompricingtesttemp.customerName
 WHERE custompricingtesttemp.partnumberSKU = custompricingtest.partnumberSKU)

and you can create index on both

  • custompricingtest.partnumberSKU
  • custompricingtesttemp.partnumberSKU

Upvotes: 0

Related Questions