UPDATE table based on the same table

Question

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.

Answer 1

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

Answer 2

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 
INNER JOIN (
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;

Answer 3

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;