Access SQL Delete problem

[alan_conoco]

Hi all,

I am trying to delete a subset of records (identified using my
InsertRemovePairs query) from a table names TradesDone.

When I execute the nested SELECT on its own it returns the correct
records. When I execute is nested in the DELETE it tries to delete
everything in my database....

What have I missed??

DELETE TradesDone.*
FROM TradesDone
WHERE EXISTS (
SELECT TradesDone.*
FROM InsertRemovePairs, TradesDone
WHERE InsertRemovePairs.TradeID = TradesDone.TradeID
AND InsertRemovePairs.AggressorBrokerID = TradesDone.AggressorBrokerID
AND InsertRemovePairs.Action = TradesDone.Action);

(Access 2000, Win NT)

Many thanks,

Alan

[Lyle Fairfield]

al*********@hotmail.co.uk wrote in news:1155133897.152618.77180
@m73g2000cwd.googlegroups.com:

Hi all,

I am trying to delete a subset of records (identified using my
InsertRemovePairs query) from a table names TradesDone.

When I execute the nested SELECT on its own it returns the correct
records. When I execute is nested in the DELETE it tries to delete
everything in my database....

What have I missed??

DELETE TradesDone.*
FROM TradesDone
WHERE EXISTS (
SELECT TradesDone.*
FROM InsertRemovePairs, TradesDone
WHERE InsertRemovePairs.TradeID = TradesDone.TradeID
AND InsertRemovePairs.AggressorBrokerID = TradesDone.AggressorBrokerID
AND InsertRemovePairs.Action = TradesDone.Action);

I understand that "IN" and "EXIST" operators have become the new standard
in place of the more traditional JOINS, but I like my Joins and in many
cases I believe they are far more efficient and safe, if more difficult to
create.

Air (=untested) SQL from me would look something like

"DELETE td.* FROM TradesDone td " _
& "INNER JOIN InsertRemovePairs irp " _
& "ON td.TradeID = irp.TradeID " _
& "AND td.AggressorBrokerID = irp.AggressorBrokerID " _
& "AND td.Action = irp.Action

--
Lyle Fairfield

[alan_conoco]

Lyle,

thanks for the inpu, unfortunately this return the error:

"Could not delete from specified tables"

The database is not read only.
I am the only person with the database open.
I do have permission to modify the database.

Any ideas on why this doesn't work??

A
Lyle Fairfield wrote:

al*********@hotmail.co.uk wrote in news:1155133897.152618.77180
@m73g2000cwd.googlegroups.com:

Hi all,

I am trying to delete a subset of records (identified using my
InsertRemovePairs query) from a table names TradesDone.

When I execute the nested SELECT on its own it returns the correct
records. When I execute is nested in the DELETE it tries to delete
everything in my database....

What have I missed??

DELETE TradesDone.*
FROM TradesDone
WHERE EXISTS (
SELECT TradesDone.*
FROM InsertRemovePairs, TradesDone
WHERE InsertRemovePairs.TradeID = TradesDone.TradeID
AND InsertRemovePairs.AggressorBrokerID = TradesDone.AggressorBrokerID
AND InsertRemovePairs.Action = TradesDone.Action);

I understand that "IN" and "EXIST" operators have become the new standard
in place of the more traditional JOINS, but I like my Joins and in many
cases I believe they are far more efficient and safe, if more difficult to
create.

Air (=untested) SQL from me would look something like

"DELETE td.* FROM TradesDone td " _
& "INNER JOIN InsertRemovePairs irp " _
& "ON td.TradeID = irp.TradeID " _
& "AND td.AggressorBrokerID = irp.AggressorBrokerID " _
& "AND td.Action = irp.Action

--
Lyle Fairfield

[Lyle Fairfield]

al*********@hotmail.co.uk wrote in
news:11********************@h48g2000cwc.googlegrou ps.com:

Lyle,

thanks for the inpu, unfortunately this return the error:

"Could not delete from specified tables"

The database is not read only.
I am the only person with the database open.
I do have permission to modify the database.

Any ideas on why this doesn't work??

Sometimes when we cannot delete it is because we have relationships which
are required to be maintained. A child record exists and this referential
integrity requires that its parent remains alive and well and living in the
table from which we are trying to delete it.

TTBOMK this will affect only specific records and not entire deletes.

So, perhaps I have some wrong syntax. Without the tables, I'm sure you
understand it's very difficult to investigate further.

--
Lyle Fairfield

[alan_conoco]

I can confirm that referential integrity is not a problem as the
database contains only one table.

Do you know of any good tutorials/API specifications for Access' SQL,
it doesn't seem to work the same way as standard SQL....

Cheers,

Alan
Lyle Fairfield wrote:

al*********@hotmail.co.uk wrote in
news:11********************@h48g2000cwc.googlegrou ps.com:

Lyle,

thanks for the inpu, unfortunately this return the error:

"Could not delete from specified tables"

The database is not read only.
I am the only person with the database open.
I do have permission to modify the database.

Any ideas on why this doesn't work??

Sometimes when we cannot delete it is because we have relationships which
are required to be maintained. A child record exists and this referential
integrity requires that its parent remains alive and well and living in the
table from which we are trying to delete it.

TTBOMK this will affect only specific records and not entire deletes.

So, perhaps I have some wrong syntax. Without the tables, I'm sure you
understand it's very difficult to investigate further.

--
Lyle Fairfield

[Lyle Fairfield]

al*********@hotmail.co.uk wrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

I can confirm that referential integrity is not a problem as the
database contains only one table.

Do you know of any good tutorials/API specifications for Access' SQL,
it doesn't seem to work the same way as standard SQL....

Cheers,

Alan
Lyle Fairfield wrote:

>al*********@hotmail.co.uk wrote in
news:11********************@h48g2000cwc.googlegro ups.com:

Lyle,

thanks for the inpu, unfortunately this return the error:

"Could not delete from specified tables"

The database is not read only.
I am the only person with the database open.
I do have permission to modify the database.

Any ideas on why this doesn't work??

Sometimes when we cannot delete it is because we have relationships
which are required to be maintained. A child record exists and this
referential integrity requires that its parent remains alive and well
and living in the table from which we are trying to delete it.

TTBOMK this will affect only specific records and not entire deletes.

So, perhaps I have some wrong syntax. Without the tables, I'm sure
you understand it's very difficult to investigate further.

--
Lyle Fairfield

Perhaps you could tell us the sql of the InsertRemovePairs Query.

--
Lyle Fairfield

[alan_conoco]

Morning,

I think I can also rule out the SQL for InsertRemovePairs as I copied
the results of this query to a new table (called irp, with no
relationthips to the other table) and used the query...

DELETE td.*
FROM TradesDone AS td INNER JOIN irp ON (td.Action = irp.Action) AND
(td.AggressorBrokerID = irp.AggressorBrokerID) AND (td.TradeID =
irp.TradeID);

which returned the same error:

'could not delete from specified table.'

are there any special properties the table or fields must have for the
DELETE to work?? Changing DELETE to SELECT returns exactly what I am
trying to remove....

thanks,

A

Lyle Fairfield wrote:

al*********@hotmail.co.uk wrote in
news:11**********************@i3g2000cwc.googlegro ups.com:

I can confirm that referential integrity is not a problem as the
database contains only one table.

Do you know of any good tutorials/API specifications for Access' SQL,
it doesn't seem to work the same way as standard SQL....

Cheers,

Alan
Lyle Fairfield wrote:

al*********@hotmail.co.uk wrote in
news:11********************@h48g2000cwc.googlegrou ps.com:

Lyle,

thanks for the inpu, unfortunately this return the error:

"Could not delete from specified tables"

The database is not read only.
I am the only person with the database open.
I do have permission to modify the database.

Any ideas on why this doesn't work??

Sometimes when we cannot delete it is because we have relationships
which are required to be maintained. A child record exists and this
referential integrity requires that its parent remains alive and well
and living in the table from which we are trying to delete it.

TTBOMK this will affect only specific records and not entire deletes.

So, perhaps I have some wrong syntax. Without the tables, I'm sure
you understand it's very difficult to investigate further.

--
Lyle Fairfield

Perhaps you could tell us the sql of the InsertRemovePairs Query.

--
Lyle Fairfield

[Lyle Fairfield]

al*********@hotmail.co.uk wrote in news:1155199771.202686.302780
@i42g2000cwa.googlegroups.com:

Morning,

I think I can also rule out the SQL for InsertRemovePairs as I copied
the results of this query to a new table (called irp, with no
relationthips to the other table) and used the query...

DELETE td.*
FROM TradesDone AS td INNER JOIN irp ON (td.Action = irp.Action) AND
(td.AggressorBrokerID = irp.AggressorBrokerID) AND (td.TradeID =
irp.TradeID);

which returned the same error:

'could not delete from specified table.'

I am sorry this has gone so wrong and that I have not been more alert.
Would you, please, try?

DELETE DISTINCTROW td.*
FROM TradesDone AS td INNER JOIN irp ON (td.Action = irp.Action) AND
(td.AggressorBrokerID = irp.AggressorBrokerID) AND (td.TradeID =
irp.TradeID);

--
Lyle Fairfield