Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Delete query with joined tables

    In Access 2020, I have a delete query like this. (Access is a front end to a SQL back end database.)

    Capture.PNG

    The SQL code is like this.

    DELETE dbo_Client.*, dbo_Client.NewClient, dbo_Order.OrderId
    FROM dbo_Client LEFT JOIN dbo_Order ON dbo_Client.ClientId = dbo_Order.ClientId
    WHERE (((dbo_Client.NewClient)=True) AND ((dbo_Order.OrderId) Is Null));

    When I run it, it says "Could not delete from specified tables."

    It appears that the query needs to specify the table to delete from in a different way. Should I make this a select query and create a separate delete query on that, or is there a simpler way?
    Last edited by Murgatroyd; 2014-05-15 at 21:13.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,512
    Thanks
    3
    Thanked 46 Times in 46 Posts
    Are the tables linked using ODBC - if so I think it is the outer join that is causing you grief. The ODBC driver has to convert that to either T-SQL or ANSI SQL and that may be the source of the problem. I would probably create a temporary table with just the ClientID of those records with no order, then do the delete using a equi-join to the temporary table.
    Wendell

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,357
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    Code:
    DELETE   * 
    FROM     dbo_Client
    WHERE   (NewClient = True) AND ClientId NOT IN (SELECT ClientId FROM dbo_Order)
    Rui
    -------
    R4

  4. The Following User Says Thank You to ruirib For This Useful Post:

    WendellB (2014-05-17)

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,512
    Thanks
    3
    Thanked 46 Times in 46 Posts
    That does look to be a more elegant approach than creating a temporary table.
    Wendell

  6. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,357
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    It may not be the most efficient way, if the orders table has many records, but it should be better than the originally proposed query .
    Rui
    -------
    R4

  7. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Thanks for your replies.

    Yes, the Access front end is linked to the SQL database back end by ODBC.

    I tried adding an extra criterion in the WHERE statement to specify a range of records by ClientId, and an ORDER BY statement to sort the results by ClientId when previewing the results as a select query, and these additions seem to work OK.

    SELECT *
    FROM dbo_Client
    WHERE (((dbo_Client.ClientId)<1000 And (dbo_Client.ClientId) Not In (SELECT ClientId FROM dbo_Order)) AND ((dbo_Client.NewClient)=True))
    ORDER BY dbo_Client.ClientId;

    However, when I switch this to a delete query and save it, Access converts the SQL code like this.

    DELETE dbo_Client.ClientId, dbo_Client.NewClient, *
    FROM dbo_Client
    WHERE (((dbo_Client.ClientId)<1000 And (dbo_Client.ClientId) Not In (SELECT ClientId FROM dbo_Order)) AND ((dbo_Client.NewClient)=True));

    This seems to work OK, but should the first line be changed back to just DELETE * each time?

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,357
    Thanks
    130
    Thanked 1,162 Times in 1,069 Posts
    Access uses it's very own delete syntax, which is pretty redundant. If it works as you need it, there is no need to change it.
    Rui
    -------
    R4

  9. The Following User Says Thank You to ruirib For This Useful Post:

    Murgatroyd (2014-05-18)

  10. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    534
    Thanks
    15
    Thanked 0 Times in 0 Posts
    OK thanks.

Posting Permissions

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