Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 10 down vote accepted

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 !!!

share|improve this answer

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, IF (LENGTH(contact.dynamicValues) > LENGTH(par.dynamicValues), contact.dynamicValues, par.dynamicValues) upd FROM tmContact par
INNER JOIN tmContact contact ON = contact.linkCompanyId AND contact.linkCompanyId IS NOT NULL
WHERE IS NOT NULL AND contact.dynamicValues <>  par.dynamicValues AND LENGTH(contact.dynamicValues) > LENGTH(par.dynamicValues)
) input ON =
SET tmContact.dynamicValues = upd;
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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