Hello and welcome to our community! Is this your first visit?
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
    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. #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,947 Times in 4,908 Posts
    As usual, just read the docs:

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

    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.
    Be yourself. No one else is as qualified.


Posting Permissions

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