MySQL - update values in table returned from query?

Question

I fired this query where I join two tables and output some of the columns of both tables:

SELECT B.option_id, B.product_id, A.title, B.identifier 
FROM `catalog_product_option_title` A JOIN
     `catalog_product_option` B
     ON A.option_id = B.option_id
WHERE A.title = "Breite"

Result:

Now I need to enter the example value xyz on the column identifier in the result, everywhere. I would go ahead and do this by hand.

How can I make use of the update statement from MySQL to solve this without having to manually change it by hand?

I tried it like this:

UPDATE `catalog_product_option`
SET identifier = 'xyz'
WHERE option_id IN (
    SELECT A.option_id 
    FROM `catalog_product_option_title` A 
    JOIN
         `catalog_product_option` B
         ON A.option_id = B.option_id
    WHERE A.title = "Breite"
)

But the simulation of this query returned that this would change 0 lines.

UPDATE

I called the sql without simulating it, and now I get this error:

1093 - Table 'catalog_product_option' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — tanaydin, Apr 15 '19 at 10:21

score 1 · Answer 1 · answered Apr 15 '19 at 10:31

Can you try like this please?

UPDATE `catalog_product_option`
SET identifier = 'xyz'
WHERE option_id IN (
    SELECT option_id FROM (SELECT A.option_id 
    FROM `catalog_product_option_title` A 
    JOIN
         `catalog_product_option` B
         ON A.option_id = B.option_id
    WHERE A.title = "Breite") as x
)

score 1 · Accepted Answer · answered Apr 15 '19 at 10:35

1

You could rewrite your query as a JOIN:

UPDATE `catalog_product_option` B
JOIN `catalog_product_option_title` A ON A.option_id = B.option_id
SET B.identifier = 'xyz' WHERE A.title = "Breite"

answered Apr 15 '19 at 10:35

Nick

138,499
22
57
95

Wow, I was not aware that we can use JOIN in UPDATE, thx man! – Black Apr 15 '19 at 10:40
1

@Black yeah it's pretty cool. There's a bit of info about it in the [manual](https://dev.mysql.com/doc/refman/8.0/en/update.html) but it's not as well documented as it could be. It should also be a lot more efficient than a nested subquery. – Nick Apr 15 '19 at 10:42

MySQL - update values in table returned from query?

1093 - Table 'catalog_product_option' is specified twice, both as a target for 'UPDATE' and as a separate source for data

2 Answers2