How to delete in MS Access when using JOIN’s?

by 
Delete Table1.*
From Table1
Where Exists( Select 1 From Table2 Where Table2.Name = Table1.Name ) = True

To expand on my answer, the official SQL specification does not provide for using Joins in action queries specifically because it can create ambiguous results. Thus, it is better (and Access is much happier) if you can avoid using Joins in action queries like I have here. The reason that Access wants DISTINCTROW is that it is likely that the Join between the two tables would create duplicates of Table1 rows (i.e., there are multiple related rows in Table2) and thus Access gets confused. I’ve also found that if you try to use a Join and a primary key does not exist Access will balk. In general, it is better to avoid a join in an action query if you can.

More Related Contents:

  1. sql in ms-access [closed]
  2. SQL multiple join statement
  3. Removing duplicate rows from table in Oracle
  4. How do I write a full outer join query in access
  5. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  6. How to do INSERT into a table records extracted from another table
  7. Multiple INNER JOIN SQL ACCESS
  8. is there a group_concat function in ms-access?
  9. Best way to delete millions of rows by ID
  10. How do I implement pagination in SQL for MS Access?
  11. [] brackets in sql statements
  12. Row numbers in query result using Microsoft Access
  13. can we list all tables in msaccess database using sql?
  14. SQL to add column with default value – Access 2003
  15. Use Access SQL to do a grouped ranking
  16. Access – compare two tables and update or insert data in first table
  17. “You tried to execute a query that does not include the specified aggregate function”
  18. How can I implement SQL INTERSECT and MINUS operations in MS Access
  19. Convert String to Date in MS Access Query
  20. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  21. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns
  22. Editing Record issues in Access / SQL (Write Conflict)
  23. How do I create a table alias in MySQL
  24. Select query with date condition
  25. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  26. Microsoft Access – Case Query
  27. SQL SET DEFAULT not working in MS Access
  28. CONCAT equivalent in MS Access
  29. Check if access table exists
  30. Access substitute for EXCEPT clause

Leave a Comment