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

Selective Table Delete question

P: n/a
Hi:

I have a table with many months of data on it. I am attempting to create a delete
transaction, based on matching a date in the table with a date in another table. The match
works perfectly in a Select Query, but when I change the query to a delete, it asks me to
specify which table I wish to delete from. I just cannot figure out how to do this, and
would appreciate a hint! I am using the Access Query interface NOT the SQL interface.

Thanks in Advance

JOhn Baker
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
DELETE A.*
FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

John Baker wrote:
Hi:

I have a table with many months of data on it. I am attempting to create a delete
transaction, based on matching a date in the table with a date in another table. The match
works perfectly in a Select Query, but when I change the query to a delete, it asks me to
specify which table I wish to delete from. I just cannot figure out how to do this, and
would appreciate a hint! I am using the Access Query interface NOT the SQL interface.

Nov 13 '05 #2

P: n/a
Thanks:

This is what it looks like now:

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
I get the error "Could not delete from specified tables". I guess I am still doing
something wrong, but dont know what!

John!

MGFoster <me@privacy.com> wrote:
DELETE A.*
FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.


Nov 13 '05 #3

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try one of these:

DELETE DISTINCTROW History.*
FROM History INNER JOIN [History LatestUpdateDate] ON
HIstory.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;

OR

DELETE *
FROM History H
WHERE weekupdated EXISTS
(SELECT * FROM [History LatestUpdateDate]
WHERE MaxOfweekupdated = H.WeekUpdated)

JET SQL is finicky about updateable queries in DELETE & UPDATE queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfXamYechKqOuFEgEQIYwwCg4/NKkTEyLHn/DiLDPG1qAD9Z5s4AoKE6
x67wp6cG7supMaQwX6hr//g3
=6qVa
-----END PGP SIGNATURE-----
John Baker wrote:
Thanks:

This is what it looks like now:

DELETE History.*
FROM History INNER JOIN [History LatestUpdateDate] ON History.weekupdated = [History
LatestUpdateDate].MaxOfweekupdated;
I get the error "Could not delete from specified tables". I guess I am still doing
something wrong, but dont know what!

John!

MGFoster <me@privacy.com> wrote:

DELETE A.*

FROM A INNER JOIN B ON A.ID = B.ID

Put the A.* (table_name.*) as the only column in the design grid.


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.