Results 1 to 12 of 12
  1. #1
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31

    Help! Deleting results of query from table or deleting from select statement

    Dear Forumers,



    Thank you for the help previously on my query. Appreciate it!

    I ran into a bit of a problem with deletes.

    Suppose I have an Access table, called sheet1. I have two queries, query1 and query2, that selects specific records from sheet1.

    Suppose I want to delete all records found via query1 and query2 from sheet1. How can I do it?

    One way I tried was using the DELETE * FROM sheet1 WHERE EXISTS (SELECT * FROM sheet1....) statement. However, I am getting errors - it deletes ALL records in sheet1 as opposed to only records returned by the SELECT* statement.

    Help!!!

    Anyone know what I did wrong, and how I can fix it?

    Thanks!!!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Show the SQL for one of your SELECT queries.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31
    I don't want to give away sensitive information, so I created a sample test with my dummy data. Here is an example.

    I created a table with 10 records.

    Here is the delete statement

    DELETE FROM Table1
    WHERE EXISTS
    (
    SELECT a.*, b.*
    FROM table1 AS a LEFT JOIN (SELECT DISTINCT last_name FROM table1 WHERE vaccinated="NO" And age<10) AS b ON a.last_name=b.last_name
    WHERE a.age_group="Adult" And a.family='Yes' And b.last_name Is Null;
    )

    The embedded SELECT statement will only return 2 of the 10 records, but the DELETE statement deletes all 10 records, instead of the 2 records it is supposed to delete.

    Thanks!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    And what happens if you simply replace the word SELECT with the word DELETE in your example?
    ------------------------------
    DELETE a.*, b.*
    FROM table1 AS a LEFT JOIN (SELECT DISTINCT last_name FROM table1 WHERE vaccinated="NO" And age<10) AS b ON a.last_name=b.last_name
    WHERE a.age_group="Adult" And a.family='Yes' And b.last_name Is Null;
    --------------------------------------
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31
    I tried your suggestion, it doesn't work. I get error from Access saying it cannot delete from specified tables.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Pine, you are creating the query in the complicate way while it can be simpler.

    I believe you are trying to delete the family who have all children vaccinated, right?

    DELETE FROM Table1
    WHERE not EXISTS
    (
    SELECT 1 FROM table1 AS a WHERE vaccinated="NO" And age<10 a.last_name=table1.last_name
    )

  7. #7
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31
    Dear Weekend,

    It's actually more complicated. I am trying to delete all families in which all children are either vaccinated or over 10 in age. If a family has at least one child under 10 who is not vaccinated, then it doesn't fit the critiera.

    The previous SELECT statement is divided into 2 parts: First part gives me all children who are both not vaccinated and under 10. The second part uses left join of original table with (children who are both not vaccinated and under 10) to get me families in which all children are either vaccinated or over 10.

    I am trying to delete all records returned by that SELECT statement.

  8. #8
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    did you try my query in post #6? it's right for your situation.

  9. #9
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31
    Dear Weekend,

    I'd prefer that my original SELECT query is not modified. The above SELECT query I listed as example is a simplified version (with made up column names) of a more complex query I am using in real life.

    What I would like really figure out is this:
    Suppose I have already two queries, query1 and query2. Query1 selects a number of records from sheet1. Query2 also selects a number of records from sheet1. Results selected from queries 1 and 2 are mutually exclusive.

    I am trying to write an Access SQL (or equivalent) to delete all records selected from query1 and query2 from sheet1.

    Thanks!

  10. #10
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    All right.
    DELETE FROM Table1
    WHERE EXISTS (SELECT 1 FROM Query1 AS a WHERE a.last_name=table1.last_name )
    or exists (SELECT 1 FROM Query2 AS b WHERE b.last_name=table1.last_name )

    you may want to embed the text of query1 and query2 inside.

  11. #11
    pinecrest515 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    31
    Thanks! Good idea...although when I ran with my actual query1 and query2, I am getting a very large timeout. The DELETE query takes way too long to run.

  12. #12
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Usually, EXISTS(),NOT EXISTS(), IN(), NOT IN() take long time to run.

    JOIN is much faster.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-12-2010, 02:11 PM
  2. Deleting empty columns from a table
    By kapil0712 in forum Programming
    Replies: 3
    Last Post: 05-17-2010, 12:35 AM
  3. Delete Query Deleting Too Much
    By TundraMonkey in forum Queries
    Replies: 6
    Last Post: 06-30-2009, 02:39 PM
  4. Deleting Spaces
    By grgerhard in forum Import/Export Data
    Replies: 2
    Last Post: 04-30-2006, 06:42 PM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums