Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: joining tables gives me an error
09-10-2013, 11:46 PM #1
- Join Date
- Feb 2007
- Thanked 1 Time in 1 Post
joining tables gives me an error
I've got two database tables, namely: original_prices and product.
Both tables have a field named product_id (key), and a field named price.
Now I want to retrieve all prices in table original_prices (field price), add $5.00 to those prices and store them into table product (field price).
I tried a couple of things myself, but I can't figure out how to do this.
Currently my coding is:
Update product set product.price=original_prices.original_price+5 from product inner join original_prices on product.product_id=original_prices.product_id
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from product inner join original_prices on product.product_id=original_prices.pr' at line 1
Hopefully someone can tell me what I'm doing wrong here.
09-11-2013, 03:30 AM #2
As usual, just read the docs:
Look for the paragraph that begins "You can also perform UPDATE operations covering multiple tables. ..."
There are other ways of expressing that, but that's the simplest.Code:UPDATE product, original_prices SET product.price = original_prices.original_price + 5 WHERE product.product_id=original_prices.product_id
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.