MySQL - UPDATE with INNER SELECT

Question

I want to do something like this, but it seems that i just dont can:

UPDATE products p2
SET
manufacturer = 
(
SELECT manufacturer
FROM products p, manufacturers m, etc.
WHERE
...other stuff...
p.name LIKE CONCAT('%',m.name,'%')    
AND p.id = p2.id
)
WHERE manufacturer = 0

The error is: #1093 - You can't specify target table 'p2' for update in FROM clause

The problem is that i have a manufacturer field in the table, but some of the manufactureres name are in the product_name-string instead of the manufacturer field, so i have to get the manufacturer id by the product_name and update the product-table its manufacturer id.

I could do this in two steps, but id like to do this in one.

Answer 1

Try this query -

UPDATE products p
  JOIN manufacturers m
    ON LOCATE(p.name, m.name) > 0
SET p.manufacturer = m.id;

...but firstly check that records are joined correctly -

SELECT * FROM products p
  JOIN manufacturers m
    ON LOCATE(p.name, m.name) > 0 -- bind records in two tables

Answer 2

A comment regarding this same error at http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html says, "MySQL does not allow to UPDATE or DELETE a table's data if you're simultaneously reading that same data with a subquery."