I need to write a query to triple the prices in a certain products category. The problem is that the product-category assignment is in another table because it's setup to allow a product to have multiple categories, so I have to join the table to get the items from category 4 in this case.
Here is the failed attempt:
UPDATE products SET products_price =3 * products_price WHERE products.products_id IN (
SELECT products.products_id
FROM products
INNER JOIN products_to_categories ON products.products_id = products_to_categories.products_id
WHERE products_to_categories.categories_id =4
) And this is the error I get back:
#1093 - You can't specify target table 'products' for update in FROM clause
So I can't select from the table I'm updating? Why doesn't it get all the results from the sub query and then feed it all at once into the parent query? I know I can just write a script to do it, but writing a massive SQL query is way more fun. 
JeevesBond posted this at 15:15 — 30th October 2008.
He has: 3,895 posts
Joined: Jun 2002
I can answer part of your question:
Because MySQL's handling of subqueries are rubbish. It treats every subquery as a correlated subquery, meaning for every result row of the outer query the inner query is run again.
Your best bet is to create a temp table, then
SELECTfrom that.Postscript: man, I've got to fix those code boxes.
a Padded Cell our articles site!
teammatt3 posted this at 23:49 — 30th October 2008.
He has: 2,076 posts
Joined: Sep 2003
This is just a hunch, but I remember reading that MySQL treats sub queries in the WHERE clause different from those in the FROM clause. (I could totally be making that up though
)
Can you rewrite the query to use the subquery in the FROM clause? You can use an AND to kind of emulate the WHERE.
*Don't run this in a production environment*
[code]
UPDATE
products
INNER JOIN
products_to_categories ON products.products_id = products_to_categories.products_id AND products_to_categories.categories_id = 4
SET
products_price = 3 * products_price
[/code]
Does that work?
My Site | Regular Expression Tester
pr0gr4mm3r posted this at 03:29 — 31st October 2008.
He has: 1,422 posts
Joined: Sep 2006
Yup, that worked.
I previously ran an alternate query where I ran the internal one from the one I posted above, took the returned IDs, and placed it in the place of the subquery, so it was something like:
UPDATE products SET products_price =3 * products_price WHERE products.products_id IN (*insert 40,000 IDs here*
)
phpMyAdmin couldn't run it because of the size alone. I had to save it in a SQL file, and import it on the good 'ol command line.
Your query is more efficient. 
PHP Starter
teammatt3 posted this at 23:49 — 31st October 2008.
He has: 2,076 posts
Joined: Sep 2003
Do you remember how long that query took to run? Was it in the minutes? I'm just curious...
pr0gr4mm3r posted this at 01:19 — 1st November 2008.
He has: 1,422 posts
Joined: Sep 2006
When I passed it through phpMyAdmin, it had a script timeout at 300 seconds into the query run, but when I ran it at the command line, it only took 5 or 10 seconds. I'm not sure why phpMyAdmin had such a problem.
PHP Starter