I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same product_id where the language_id is 1.

So far I've tried the following query, but I'm getting errors indicating that MySQL doesn't want to update a table where the table's also being used in the subquery.

  products_description AS pd
  pd.products_seo = (
      products_description AS pd2
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  pd.language_id <> 1

Is there a "simple" way around this limitation in MySQL? Or any "tricks"? I'm a little surprised that my query doesn't work, as it seems logical.

3 Answers 3


This is rather risky business, and I can understand why. It has to do with the way MySQL processes subqueries. I wrote about it back on Feb 22, 2011 : Problem with MySQL subquery

Performing JOINs involving SELECTs and subquery SELECTs are OK. On the flipside of things, UPDATEs and DELETE can be a rather death-defying adventure.


Try refactoring the query so that it is an INNER JOIN of two tables

    products_description pd INNER JOIN products_description pd2 ON
    (pd.products_id=pd2.products_id AND pd2.language_id=1 AND pd.language_id<>1)
SET pd.products_seo = pd2.products_seo;

Give it a Try !!!


Well this did not work for me, the update just didn't happened even though there were matching rows. What I had to do is create the other table as subquery so temporary file is used.

UPDATE tmContact 
SELECT par.id, IF (LENGTH(contact.dynamicValues) > LENGTH(par.dynamicValues), contact.dynamicValues, par.dynamicValues) upd FROM tmContact par
INNER JOIN tmContact contact ON par.id = contact.linkCompanyId AND contact.linkCompanyId IS NOT NULL
WHERE contact.id IS NOT NULL AND contact.dynamicValues <>  par.dynamicValues AND LENGTH(contact.dynamicValues) > LENGTH(par.dynamicValues)
) input ON input.id = tmContact.id
SET tmContact.dynamicValues = upd;
update products_description pd1,
 (select products_seo from products_description where language_id = 1) pd2
set pd1.products_seo = p2.products_seo
where pd1.products_id = p2.products_id and pd1.language_id <> 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.