Access97 SQL Syntax

[JA1]

[RESOLVED] Access97 SQL Syntax

I'm familiar with SQL but not with Access97 - some wierd syntax here!

I'm creating a Query that is to Delete records from a table depending on a non-key field in a second table.

I first tried an INNER JOIN

DELETE Activity_Preferences.*
FROM Activity_Preferences INNER JOIN Activity_Key
Activity_Preferences.Activity_Key=Activity_Key.Activity_Key AND Activity_Key.Complete=1;

which was initially accepted but then complained about the INNER JOIN after I went in a second time to 'fix' the error.

I've finally got the following syntax accepted:-

DELETE Activity_Preferences.*
FROM Activity_Preferences, Activity_Key
WHERE Activity_Preferences.Activity_Key=Activity_Key.Activity_Key AND Activity_Key.Complete=1;

But when executed still gets the following error (same as for the INNER JOIN):-

'Operation must use an updatable query'.

What else is needed?

J.

 

[namliam]

You do not have the proper primary keys and stuff set.... Try doing below... that should work.

DELETE Activity_Preferences.*
FROM Activity_Preferences
where Activity_Preferences.Activity_Key in (Select Activity_Key.Activity_Key
from Activity_Key
WHERE Activity_Key.Complete=1);

 

[JA1]

I MUCH prefer real RDBMS databases

Thanks for that, actually I tried something like that first, but the syntax checker hated it...so I went down the INNER JOIN route.

Just found out that checkboxes 'checked' = -1

Ah well,

Thanks again!

J.

 

[The_Doc_Man]

Joined queries might not be updatable depending on the relationship implied in the JOIN. That might be a reason that the query is not updatable.

If you are trying to delete records based on a one-to-many join, you cannot delete the parent record if even one case exists with two children of the same parent.

Typically, you mark child records for deletion, then mark parent records for deletion, but then go back and REVOKE the parent's deletion mark if it has another child that is not being deleted. Then delete all marked children. Then delete all marked parents, knowing that some parents were "saved" from deletion.

This presumes that you have set up referential integrity or that you WANTED to set up RI and might still do so.

Doing this in any other order violates the basis of referential integrity.

 

[Pat Hartman]

The first syntax using the Inner Join would have been updatable if both the joined tables could be updated. The second syntax that just used the coma, would NEVER be updatable since it creates a cartesian product (also known as a cross join).