CODEWITHSUNDEEP
mysql
Michael Emerson
Michael Emerson

Reputation: 1813

MySQL how to UPDATE a field in one table based on values in another table

This may have been asked before but my issue is maybe more complex.

I am working with an OpenCart database and need to update the images for categories. The image names are the same as the category names, just lowercase and appended with '.jpg'. The problem is, I only want to update specific entries, ones that are within a certain parent ID.

My SELECT to get the relevant category IDs is this:

SELECT c.category_id FROM oc23_category c LEFT JOIN oc23_category_description cd ON cd.category_id = c.category_id WHERE c.parent_id = 71

So I attempted to use this SELECT statement to reference where to update the images:

UPDATE oc23_category SET image = LOWER(oc23_category_description.name) WHERE category_id IN(SELECT c.category_id FROM oc23_category c LEFT JOIN oc23_category_description cd ON cd.category_id = c.category_id WHERE c.parent_id = 71);

But the error I get is:

You can't specify target table 'oc23_category' for update in FROM clause

I'm not sure what this means or how to rectify it. Any help with this is appreciated, thank you.

Upvotes: 1

Views: 38

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Use join instead of sub query

UPDATE 
  oc23_category c 
JOIN oc23_category_description cd 
     ON cd.category_id = c.category_id 
SET
  c.image = LOWER(cd.name) 
WHERE c.parent_id = 71

Upvotes: 1

Related Questions