Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Feb 2007
    Posts
    217
    Thanks
    25
    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:

    PHP Code:
    Update product set product.price=original_prices.original_price+5 from product inner join original_prices on product.product_id=original_prices.product_id 
    But that gives me the following error:

    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    As usual, just read the docs:
    http://dev.mysql.com/doc/refman/5.5/en/update.html

    Look for the paragraph that begins "You can also perform UPDATE operations covering multiple tables. ..."

    Code:
    UPDATE product, original_prices
    SET product.price = original_prices.original_price + 5 
    WHERE product.product_id=original_prices.product_id
    There are other ways of expressing that, but that's the simplest.
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •