12

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.

20

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

| improve this answer | |
0

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;
| improve this answer | |
-2
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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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