Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    47

    Unanswered: compare & delete ....should be easy ....

    I have two tables , [product] and [discontinued] , I want to compare [product.product_number] to [discontinued.ItemID] and if they are equal I want to delete the entire record in [product]

    Can somebody point me in the right direction ?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    DELETE product
    WHERE product_number IN (SELECT ItemID FROM discontinued)



    A little schema speculation.. perhaps you should use a status flag/bit on your product table instead of keeping a seperate discontinued table? I don't really know what you're doing exactly, but it's just a thought.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2003
    Posts
    47

    love to add

    I would love to add that flag but alas the destination table (product) is preformated for a shopping cart and I am not adept enough just yet to add a field for that flag without the shopping cart crashing on it , and my supplier is giving me inventory updates in a totally different format with basically ItemID being the only field that I can use to correlate and update my inventory . Not the best of scenarios but until I understand all this better , I'm kind of stuck with it .

    Thanks for the code , I'm going to jump right into it !

  4. #4
    Join Date
    May 2003
    Posts
    47

    syntax error ?

    DELETE product
    WHERE product_number IN (SELECT ItemID FROM discontinued)

    is giving me a syntax error "missing operator", when I paste it into the Sql view window ?

  5. #5
    Join Date
    May 2003
    Posts
    47

    nevermind , got it

    just a goof on my part sorry and thanks !

Posting Permissions

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