Skip to main content
A
Anonymous
March 17, 2008
Question

SQL problems

  • March 17, 2008
  • 1 reply
  • 418 views
In Access 2000, I have a table called Nikon_Prices that I need to update to the new prices. I've gotten the new prices in Excel format and converted them to Access and put this new table, 4_2008Prices into the overall database. So I'm trying to:

update Nikon_Prices
set ListPrice, Description
where PartNumber (from table Nikon_Prices) = PartNumber (from table 4_2008Prices).

I need to replace all the old Nikon prices with the new prices. There are non-Nikon prices in table Nikon_Prices that I don't want to change. Any help?
    This topic has been closed for replies.

    1 reply

    P
    paross1
    Participating Frequently
    March 17, 2008
    One of these methods might do the trick. (Access is kind of "funny", and I haven't had to do an update that depended on data from another table in Access in a long time.)

    UPDATE Nikon_Prices n
    SET n.ListPrice = (SELECT p.ListPrice
    FROM 4_2008Prices p
    WHERE n.PartNumber = p.PartNumber)
    WHERE EXISTS(SELECT 1
    FROM 4_2008Prices p
    WHERE n.PartNumber = p.PartNumber)

    --or--

    UPDATE Nikon_Prices
    SET ListPrice = n.ListPrice
    FROM Nikon_Prices n, 4_2008Prices p
    WHERE n.PartNumber = p.PartNumber

    Phil
    A
    Anonymous
    March 17, 2008
    Neither one worked, possibly because i don't understand the use of the -n or -p. brendan
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices