Solved

Could not delete from specified tables

Posted on 2004-08-08
16
1,232 Views
Last Modified: 2012-08-14
OK, this seems silly.

I have a query which is updateable when viewed as a table in Access (can insert, delete, update). Using queries, I can insert and update, but not delete.

A delete query against this query gives the error 'Could not delete from specified tables', even though I can do the same thing by viewing qb in datasheet mode, selecting all rows and pressing Delete.

Here's pseudocode-SQL for a simplified version of the system with the problem:
   CREATE TABLE a (
     a_id AutoNumber PRIMARY KEY,
     a_data Text(50)
   );
   CREATE TABLE b (
     a_id Number REFERENCES a (a_id) ON DELETE CASCADE,
     b_id Number,
     b_data
   );
   CREATE Query q AS
     SELECT a.a_id AS a_a_id, a_data, b.a_id as a_id, b_id, b_data
       FROM a INNER JOIN b ON a.a_id = b.a_id;

Now, you can delete from q in datasheet mode, but not with something like:
   DELETE FROM q;

How can I programmatically delete from q? Or do I have to generate a page of SQL in all my scripts to do what q is meant to do?
0
Comment
Question by:burtdav
16 Comments
 
LVL 1

Expert Comment

by:perennial
ID: 11749488
>>>DELETE FROM q;

Delete what from q.

Delete * from q; >> this will delete all from table q.

or give a condition:

delete * from q
where field1 < 1;

0
 
LVL 5

Author Comment

by:burtdav
ID: 11749535
Thanks, mate, but I'm quite up on my SQL.

"DELETE FROM q" = "DELETE * FROM q"

Any help on my question?
0
 
LVL 3

Expert Comment

by:realrael
ID: 11749633

if i got it right you have table a linked to table b. it could be that you have to delete entries from table b first before you delete associated entries from table a. might be a referential integrity security check. let me know if this works. - rael
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 5

Author Comment

by:burtdav
ID: 11749673
I don't think so: b is the child table, so each row in q corresponds to one row in b, not a, so a delete implies a delete in b, not a.
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 11749906
When you delete from q in datasheet mode, are all the records in both tables "a" and "b" deleted, or just those of table "b" ?
0
 
LVL 5

Author Comment

by:burtdav
ID: 11750065
Only b.

Note I can do something like
   INSERT INTO q (a_data, b_id, b_data) VALUES ('x', 1, 'z');
to insert a record into both tables.

I can also update through to both tables through q.
0
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 500 total points
ID: 11750467
you cannot do that

jet will not accept that, regardless wether the involved jionfields are keys or not
tis is no bug: if you do a delete query with the wizard, you only get tables to choose as datasource
(same for append queries, but here the sql works, if you at least adress the both joinfields)

i doubt, that other rel-db engines support that either
you easily can shoot into yor own knee by diong that, so they wont let you

Regards, Franz
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11750520
so how can you delete from table A and B records based on a query testq:

you wil need to do a query for each table, fetching the criteria from a subquery:

DELETE *
FROM a
WHERE (([a].[a_id]=(SELECT [q].[ a_a_id] FROM q )));

and

DELETE *
FROM b
WHERE (([b].[a_id]=(SELECT [q].[ a_id] FROM q )));

if you need restictions, fomulate them as WHERE clause in the subquery

Regards, Franz
0
 
LVL 5

Author Comment

by:burtdav
ID: 11750832
OK, bonjour-aut,

That sucks, but it sounds like good information.

I really don't see the ambiguity - it's a fully updatable query (even Access agrees in all other cases), and I'm sure you can do this in other RDBMSes. And so I consider it a bug.

But on to practicality:
   DELETE FROM b
   WHERE b.a_id = (SELECT a_id FROM q);
is unfortunately indeed what I need to do. But my real-life problem has multiple key fields, and Jet doesn't support
   WHERE (a, b, c) IN (SELECT ...)

How do you do a multiple-field join against a subquery with Access? (Because you can't delete from anything joined directly to q, can you?)
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11750952
in fact you are relatively free, if you sticly keep to the priciple, only to adress one table and fields from this table in the main-query and do the right side of the WHERE-clause as subquery

so  DELETE FROM b   WHERE b.a_id = (SELECT a_id FROM q); will delet all records from b, where the a_id is the same as field a_id of query q
if query q is a complex query joining several tables, it does not matter as long as it gives only one field, containing usefull values in terms of matching against the leftsided field of your table, from where you want to delete

so to your real life problem:
if (a,b,c,) are fields of the table (lets call it myTab), than it would result in:

DELETE FROM b
   WHERE myTab.a = (SELECT ...subqueryfor values corresponding to a..) AND myTab.b=(SELECT ...subqueryfor values corresponding to b..)  AND myTab.c=(SELECT ...subqueryfor values corresponding to c..) ;

if (..b,c) are contained in different tables and you want to delete all records in all three tables based on the query q , it becomes tricky, because they cannot run at the same time and after the first deletequery, the query q will not give you the records based on the  a,b,c, any longer as a is already deleted. so this would be taken care of by dong a temporary table from q and run the subqueries in the delete queries agianst that table instead of query q.

regards, Franz

 
0
 
LVL 5

Author Comment

by:burtdav
ID: 11752215
I'm talking about a real-life problem here, so my equivalent SQL for three subqueries in normal use of my application would literally fill a page; add to that the issue that it involves executing the subquery three times, which would be a significant and entirely unnecessary performance hit, and finally, it doesn't even work. You can't apply = to a multi-row set. And if you change it to IN, it becomes a join on (a=a OR b=b...) instead of AND. So I still haven't got a better way of doing the join than concatenating the fields, which is really really slow.

Review: what I really want (and, IMO, should be able to do) is this:
   DELETE
     FROM q
     WHERE (conditions);

This is too slow, and too ugly, but the best I've got so far:
   SELECT a_id, b_id
   DELETE
     FROM b
     WHERE a_id & '/' & b_id IN
       (SELECT a_id & '/' & b_id
         FROM q
         WHERE (conditions));

I tried this, but it doesn't work, either: "Could not delete from specified tables"
   SELECT a_id, b_id
     INTO t
     FROM q
     WHERE (conditions);
   DELETE b.*
     FROM b INNER JOIN t ON b.a_id = t.a_id AND b.b_id = t.b_id;
   DROP TABLE t;

It's bedtime for me...  another day, a fresh look at an annoying problem.
0
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 11759823
hi burtdav,

what i tested:

you cannot do a

DELETE 1stTab.* FROM 1stTab INNER JOIN 2ndTab ON (...)  

in fact only accepted method from Jet to involve values outside 1stTab is a subquery.

so you need something like

DELETE 1stTab.* FROM 1stTab WHERE 1stTab.somefield=(corresponding to some value form whatever source)

so the right side of the WHERE clause can be virtually any table or query, also a query like q, which sets fields from 1stTab in some restricted relation to sme other tables - only restriction is, that a subquery must not have more than one field as result.

so even if you have a complex query in your real-life, this should be not too complex if your real-lif q is big and compex, do a query between, having only a few fields and use that for the DELETE WHERE clause

Regards, Franz
0
 
LVL 5

Author Comment

by:burtdav
ID: 11760331
As far as I know, there's no way to delete records in one table based on multiple fields in another table without concatenating those fields in a costly way.

The way you're been suggesting (subquery) only allows matching on a single field, in Jet.

You can do this:
   DELETE
     FROM b
     WHERE EXISTS
       (SELECT *
         FROM q
           WHERE (conditions)
             AND b.a_id = q.a_id
             AND b.b_id = q.b_id);

But then I found this - DISTINCTROW:
   SELECT a_id, b_id
     INTO t
     FROM q
     WHERE (conditions);
   DELETE DISTINCTROW b.*
     FROM b INNER JOIN t ON b.a_id = t.a_id AND b.b_id = t.b_id;
   DROP TABLE t;

One of these will probably be my solution. Until Microsoft fix their product.
0
 
LVL 5

Author Comment

by:burtdav
ID: 11760340
Thanks for your help, bonjour-aut. There's a lot of bad information, and your SQL is kinda poor, but you've definitely helped me work through this problem.
Cheers
0
 

Expert Comment

by:Erez_M
ID: 11880441
I encountered similar problems in Access, and have some solutions:

A.  Use the DISTINCTROW.  Whenever using update/delete of JOINs.

B. Do not exceed 10 "ON" conditions.  Otherwise, I get the "could not delete from tables".
 - Erez
0
 
LVL 5

Author Comment

by:burtdav
ID: 11880651
Thanks Erez, but...
A. DISTINCTROW doesn't help here unfortunately - it seems to me you simply cannot delete from a QueryDef that includes a join.
B. I have only 3 join (ON) conditions.

For the question's completeness, I'll note that this is how I have implemented my solution:
   DELETE
     FROM b
     WHERE EXISTS
       (SELECT *
         FROM q
           WHERE (conditions)
             AND b.a_id = q.a_id
             AND b.b_id = q.b_id)
       AND b.a_id = (a_id value);

It uses no temp table as my other would, which would cause concurrency headaches.

Adding part of the key to the outside of the query speeds things up a lot - it executes q against each row in b, otherwise, and that takes a very long time. With a_id specified, it's quick.

To conclude: real DBMSes allow
   DELETE
     FROM q
     WHERE (conditions);
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access report groups with sums 5 28
Any Way to Print an Import Spec? 3 31
Access on Mouse move 5 35
date criteria to pull up records for the last 3 months 39 42
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question