Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

UPDATE
  products_description AS pd
SET 
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM 
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  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
add comment

1 Answer

up vote 5 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.

SUGGESTION

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

UPDATE
    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
    
Brilliant! Worked like a charm! Thanks so much, I'll definitely be keeping that one in my back pocket :) –  nathangiesbrecht Apr 30 '13 at 21:48
add comment

Your Answer

 
discard

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.