MySQL Question - Updating Prices in Huge Table
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,956 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,102 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?
pr0gr4mm3r posted this at 03:29 — 31st October 2008.
He has: 1,502 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. 
teammatt3 posted this at 23:49 — 31st October 2008.
He has: 2,102 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,502 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.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.