By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,838 Members | 1,606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,838 IT Pros & Developers. It's quick & easy.

Why can't I delete from an updatable query

P: n/a
Hi, Access officionados,

I'm new here, so please cut me slack/gently tell me off if I'm out of line
or in the wrong place.

OK, here's something that seems silly (and is also problematic to me).

I have a query which is updateable when viewed as a datasheet in Access (you
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 Text(50),
PRIMARY KEY (a_id, b_id)
);
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;

My practical questions are these: 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?

But I'm also interested in other people's reactions to this bug/feature, and
even if anyone wants to try justifying the behaviour...

Cheers,
Dave
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mon, 09 Aug 2004 09:30:18 GMT, "Dave Burt" <bu*****@hotmail.com>
wrote:
Hi.
Yes, interesting. It seems the updateable property is being set to
true inconsistently with how the execute works.
However you shouldn't have a problem. When you delete manually from
the query datasheet it is actually only deleting from table b, which
you can with a query like

DELETE b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

Or you can delete from table a if you want the cascaded deletes in
table b.

Excuse me for top-posting
David
Hi, Access officionados,

I'm new here, so please cut me slack/gently tell me off if I'm out of line
or in the wrong place.

OK, here's something that seems silly (and is also problematic to me).

I have a query which is updateable when viewed as a datasheet in Access (you
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 Text(50),
PRIMARY KEY (a_id, b_id)
);
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;

My practical questions are these: 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?

But I'm also interested in other people's reactions to this bug/feature, and
even if anyone wants to try justifying the behaviour...

Cheers,
Dave


Nov 13 '05 #2

P: n/a
Thanks for your thoughts.

What do you mean by "the updateable property being set to true
inconsistently with how the execute works"?

Just a minor correction: that SQL doesn't quite work - you need DISTINCTROW:

DELETE DISTINCTROW b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

What is the point of DISTINCTROW anyway? It's certainly not defined in SQL
standards, and it seems to me just to complicate things. Anyway...

And what performance penalty will I pay for the following alternative?

DELETE FROM b
WHERE EXISTS
(SELECT *
FROM q
WHERE (conditions)
AND b.a_id = q.a_id
AND b.b_id = q.b_id);

I prefer this because in my particular application, q calculates a number of
columns that I'd like to build a general WHERE clause against, and use that
in multiple ways, selecting and updating as well as deleting from q.

I guess the question remains, too, what's wrong with:

DELETE DISTINCTROW * FROM q;

?

"David Schofield" <d.***************@blueyonder.co.uk> wrote:
Hi.
Yes, interesting. It seems the updateable property is being set to
true inconsistently with how the execute works.
However you shouldn't have a problem. When you delete manually from
the query datasheet it is actually only deleting from table b, which
you can with a query like

DELETE b.*
FROM a INNER JOIN b ON a.a_id = b.a_id
WHERE (((a.a_data)="aaa1"));

Or you can delete from table a if you want the cascaded deletes in
table b.

Excuse me for top-posting
David

On Mon, 09 Aug 2004 09:30:18 GMT, "Dave Burt" <bu*****@hotmail.com>
wrote:
... query which is updateable when viewed as a datasheet in Access (you
can insert, delete, update). Using queries, I can insert and update, but notdelete.

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 Text(50),
PRIMARY KEY (a_id, b_id)
);
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;

<snip>
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.