Deletion not done due to Error 3086

[hklein]

Hi, I made a query, which is intended to delete records from tbl Status which doesn't exist tbl Clients. The sql view of the query is like this:

 DELETE Status.*, clients.ID FROM Status LEFT JOIN clients ON Status.ID = clients.ID WHERE (((clients.ID) Is Null));

The syntax seems to be ok, on datasheet view it is shown, which records would be deleted, but when trying to run the query, it is stopped with error 3086 (table is read only etc) Does anybody knows the reason of this? Thanks

[June7]

Gets tricky. Requires subquery.

Google: SQL Delete not match

Review: http://www.youdidwhatwithtsql.com/up...ther-table/153

[John_G]

Try this SQL:

DELETE FROM Status Where Status_ID not in (Select Client_ID from Clients)


Which says: Delete any records in the Status table where the Status_ID is not found in the list of Clients (Client_ID)

Just as a thought - are you sure you mean to use Status_ID? Does the Status_ID field really contain a Client number?
(It's not wrong to do that, but the field name Status_ID normally would be the PK for the Status table)

John