Styjun

I have two tables products and filters. My products table is like below:-

id name color_id size_id composition_id
 1 Test Black 60x60 CM Cotton
 2 Test2 Red 60X90 CM Acryllic

My filters table as below:-

id filter_name
 1 Black
 2 Red
 22 60x60 CM 
 23 60X90 CM
 61 Cotton
 62 Acryllic

My Expected output like below:-

id name color_id size_id composition_id
 1 Test 1 22 61
 2 Test2 2 23 62

I have tried below query that is working for only color_id. Query is below:-

UPDATE products
INNER JOIN filters ON products.color_id = filters.filter_name
SET products.color_id = filters.id

I want to update all ' color_id,size_id,composition_id' in one update statement. May be we need to use Case Statements. Can anyone help?

One way you can do this is with a set of correlated subqueries, one for each column that you need to look up. We use a COALESCE so that if the value is not found, we retain the previous value:

UPDATE products p
 SET color_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.color_id), color_id),
 size_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.size_id), size_id),
 composition_id = COALESCE((SELECT id FROM filters WHERE filter_name = p.composition_id), composition_id)

Then you can SELECT * FROM products:

id name color_id size_id composition_id
1 Test 1 22 61
2 Test2 2 23 62

Demo on dbfiddle

You can also achieve the same result with an UPDATE ... JOIN query, which might be more efficient:

UPDATE products p
LEFT JOIN filters f1 ON f1.filter_name = p.color_id
LEFT JOIN filters f2 ON f2.filter_name = p.size_id
LEFT JOIN filters f3 ON f3.filter_name = p.composition_id
SET color_id = COALESCE(f1.id, color_id),
 size_id = COALESCE(f2.id, size_id),
 composition_id = COALESCE(f3.id, composition_id)

Demo on dbfiddle