Could not delete from specified tables

>>>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;

Thanks, mate, but I'm quite up on my SQL.

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

Any help on my question?

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

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.

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" ?

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.

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

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?)

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

 

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.

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

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.

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

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

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);