CODEWITHSUNDEEP
mysqlsqldatabase
harunB10
harunB10

Reputation: 5207

Fastest way to compare and update milion rows

I have a table of customers which I need to update and replace one column(varchar) with an id (integer). Let's call the first table Customers and the second Products So, instead of having varchar (for example, Product A), I want to have an id of that product (foreign key). Products are in another table which have id and productName:

I've tried this:

UPDATE customers 
SET s_product = (SELECT id from products WHERE products.productName = customers.s_product)

But it takes looong time and I need a faster way to do this. Also I've tried to set index on customers.s_product (BTREE and FULLTEXT), but still no improvement.

Upvotes: 1

Views: 75

Answers (2)

Anil Agrawal
Anil Agrawal

Reputation: 3026

Create indices on both the tables as

products(productName) & customers(s_product)

And execute below query

UPDATE customers t1, products t2
    SET t1.s_product = t2.id
    WHERE t1.s_product = t2.productName;

Upvotes: 2

brhn.lok
brhn.lok

Reputation: 53

you can try join statement here

update customers as c join products as j on c.s_product = j.productName set c.s_product = j.id

ref : http://www.mysqltutorial.org/mysql-update-join/

Upvotes: 2

Related Questions