Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Sydney, Australia
    Posts
    12

    Unanswered: SQL Delete Query

    Hi there,

    I am trying to get the SQL right for a Delete Query I am trying to run on a table and am not being very successful.

    Basically, I have two tables:

    Table1 - Items
    -----------------
    Primary Key: ItemID
    AdditionalField: ItemNumber (type: String)

    Table2 - Prices
    -----------------
    Primary Key: CustomerPriceID
    Foreign Key: ItemID (from Items table)

    I want to delete a specific record/s from the Prices table, based on an ItemNumber parameter entered by the user.

    My query is as follows:

    DELETE * FROM tblPrices INNER JOIN tblItems ON tblPrices.ProductID = tblItems.ItemID WHERE tblItems.ItemNumber = USERPARAMETER

    but I am getting a runtime Error (3083) when run in MS Access.

    I don't want to delete anything from the Items table, just the Prices table.

    Any advice?

    Thanks

    Rael

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    DELETE FROM tblPrices p
    WHERE EXISTS(
    Select null
    from tblItems i
    where i.ItemNumber = USERPARAMETER AND
    i.ItemID = p.ProductID);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2003
    Location
    Sydney, Australia
    Posts
    12
    Thanks... works perfectly!

    Rael

    Originally posted by r123456
    DELETE FROM tblPrices p
    WHERE EXISTS(
    Select null
    from tblItems i
    where i.ItemNumber = USERPARAMETER AND
    i.ItemID = p.ProductID);

Posting Permissions

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