MySQL error updating a table using join

Question

I am trying to update a table in a mysql database, and am getting a syntax error. It is a MyISAM table if that matters.

Here is the sql

UPDATE product SET price=(price*1.0909)
JOIN product_to_category ON product.product_id = product_to_category.product_id
WHERE category_id =6
OR category_id =1
OR category_id =2

My goal is to get a list of products from 3 specific categories (information from the *product_to_category* table) and increase the price by about 10%. The price is contained in the product table.

From what I see in the documentation I can use join in the update statement, and I have done similar queries in the past.

This is a production website, which currently has about 40,000 products. If needed I can do a php script that will loop through the products and do it one by one, but it seems like I should be able to do it directly from mysql.

John Woo · Accepted Answer · 2013-09-29 00:00:52Z

1

Your statement is a little bit messed up. SET follows after JOIN which is part of the UPDATE clause.

UPDATE product 
       JOIN product_to_category 
           ON product.product_id = product_to_category.product_id
SET    price = price * 1.0909
WHERE  category_id IN (1,2,6)

answered Sep 29, 2013 at 0:00

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Developer Gee Over a year ago

Worked like a charm, I knew I wasn't too far off. Makes perfect sense now, but I stared at that for a long time trying to figure out where I went wrong.

Collectives™ on Stack Overflow

MySQL error updating a table using join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related