Solved

Could not delete from specified tables

Posted on 2004-08-08
16
1,218 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
Comment Utility
>>>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
Comment Utility
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
Comment Utility

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
 
LVL 5

Author Comment

by:burtdav
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

by:burtdav
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now