• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1286
  • Last Modified:

Could not delete from specified tables

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
burtdav
Asked:
burtdav
1 Solution
 
perennialCommented:
>>>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
 
burtdavAuthor Commented:
Thanks, mate, but I'm quite up on my SQL.

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

Any help on my question?
0
 
realraelCommented:

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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
burtdavAuthor Commented:
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
 
NestorioCommented:
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
 
burtdavAuthor Commented:
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
 
bonjour-autCommented:
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
 
bonjour-autCommented:
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
 
burtdavAuthor Commented:
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
 
bonjour-autCommented:
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
 
burtdavAuthor Commented:
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
 
bonjour-autCommented:
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
 
burtdavAuthor Commented:
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
 
burtdavAuthor Commented:
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
 
Erez_MCommented:
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
 
burtdavAuthor Commented:
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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now