Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update with different primary keys (Access 2000)

    Can i update 2 tables with different primary keys? I need to replace the prices in the field ddu of the table
    Products with the prices from the field ddu of the table NewPrices.
    However, the table Products has a primary key ProductId, and the field DDU is a number.
    Whereas the table NewPrices consist of only 2 fields: Code,with a primary key and ddu which is a number.
    The table NewPrices lacks the field ProductID

    What i tried is the following, i show only the relevant line of the function:



    UPDATE products INNER JOIN NewPrices ON products.code = NewPrices.Code SET products.ddu = NewPrices.Code


    I receive no error but the ddu prices in the table products are not updated.I think the reason is that there is
    no field productID in the second table in order for access to compare it.
    Is there any possibility at all to replace the ddu prices in the table products with those of the table NewPrices?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: update with different primary keys (Access 2000)

    At the least, I would expect you to set Products.DDU to NewPrices.DDU:

    UPDATE ... SET Products.DDU = NewPrices.DDU

    Does the table Products contain a field Code? And if so, is it related to the field Code in the table NewPrices?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: update with different primary keys (Access 2000)

    Your SQL is trying to set the DDU value in Products equal to the Code value in NewPrices. Join the two tables on Code and update the DDU field in one from the DDU value in the other.
    Charlotte

Posting Permissions

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