Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    50

    Unanswered: delete query question - "couldn't delete from specified tables"

    Does anyone see any reason the following delete query (SQL) shouldn't work?
    Trying to run the query gets a "couldn't delete from specified tables" message.

    Thanks in advance.


    DELETE [Small - Price$].*, [HazelFile].Page_Number, [HazelFile].POP
    FROM [Small - Price$] INNER JOIN [HazelFile] ON [Small - Price$].SKUID = [HazelFile].SKUID
    WHERE ((([HazelFile].Page_Number) Not Like "*") AND (([HazelFile].POP) Like "POP"));
    Who imported me here?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Tunkstar

    I am guessing hear, but on the basis that yiu just want to delete records from the [Small - Price$] table, then I think the SQL should be

    DELETE [Small - Price$].*
    FROM [Small - Price$] INNER JOIN [HazelFile] ON [Small - Price$].SKUID = [HazelFile].SKUID
    WHERE ((([HazelFile].Page_Number) Not Like "*") AND (([HazelFile].POP) Like "POP"));


    I believe you can only delete records from ONE table (on the many side if joined) with a delete SQL !?

    HTH

    MTB

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Delete queries are funny little fellas. You can't delete from a table in a query that links to other tables. The reason, I guess, is that the process of deletion would invalidate the link... which is how you are retrieving your data... so if you delete the data the link is invalid and you couldn't derive the data you wanted to delete. Vicious circle?

    Anyway - try a corrolated SubQ:

    Code:
    DELETE * 
    FROM [Small - Price$]
    WHERE EXISTS (SELECT NULL FROM [HazelFile] WHERE [Small - Price$].SKUID = [HazelFile].SKUID AND [HazelFile].Page_Number Not Like "*" AND [HazelFile].POP Like "POP")
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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