How to update multiple values in mysql based on another table using join

Question

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?

Nick · Accepted Answer · 2019-03-22 05:11:17Z

2

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

edited Mar 22, 2019 at 5:11

answered Mar 22, 2019 at 5:05

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

kunal Over a year ago

Your way is right but what if my 100 records are already updated and i again run this query... above 100 records will be empty .. Hope you understand

Nick Over a year ago

@kunal I didn't realise you might run the query multiple times. I've updated it (and the demo) for that situation

kunal Over a year ago

actually my client don't want to fill ids in column.. So my client gives csv sheet with names every week so i will update the sql query manually . Hope you understand

Nick Over a year ago

@kunal I can't say I blame them - much easier to write the words! But the new queries I have posted should do what you want.

Nick Over a year ago

COALESCE looks at each of its arguments, starting from the left, and returns the first one that is not NULL. So for example, in the second query, if there is no match for color_id in filters, f1.id will be NULL and the COALESCE(f1.id, color_id) will return the original value of color_id

|

Collectives™ on Stack Overflow

How to update multiple values in mysql based on another table using join

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related