Multiple LEFT JOIN in Access

sql ms-access sql-delete jet
grjj3 picture grjj3 · Dec 20, 2011 · Viewed 36.9k times · Source

I have the following query, which works for MySQL:

DELETE `test1`, `test2`, `test3`, `test4` FROM
`test1` LEFT JOIN `test2` ON test2.qid = test1.id
LEFT JOIN test3 ON test3.tid = test2.id
LEFT JOIN test4.qid = test1.id
WHERE test1.id = {0}

But it doesn't work for MS Access. I've tried to add parentheses around the LEFT JOIN, but it gives me syntax error in FROM clause. So how should this query look in order to work in MS Access?

Answer

Olivier Jacot-Descombes picture Olivier Jacot-Descombes · Dec 20, 2011

The Access DELETE requires a star (*): DELETE * FROM ...

In addition, the joins must be nested by using parentheses:

DELETE test1.*, test2.*, test3.*, test4.*
FROM
    (
      (
        test1 
        LEFT JOIN test2 ON test1.qid = test2.id
      )
      LEFT JOIN test3 ON test2.tid = test3.id
    )
    LEFT JOIN test4 ON test1.qid = test4.id
WHERE test1.id = {0}

This is specific to Access (Jet) SQL.

Related questions

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

I am attempting to use the DELETE clause in MS Access and have an issue when also using the JOIN clause. I have notice this can be accomplished by using the DISTINCTROW key word. For example, the following SQL statement …

Read More
How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More