Delete Query with criteria in a different table 1

[AccessHelp123]

Hi,

I am trying to figure out how to write a query which deletes from table1 all values that have one particular field matching in table2
and one particular field that does not match.



I wrote something like this but it gives me an error.

DELETE tableTemp.*
FROM tableTemp INNER JOIN tableTempNccIs1Compare ON [tableTempNccIs1Compare].[cellIdentity] = [tableTemp].[cellIdentity]
WHERE [tableTempNccIs1Compare].[locationAC] <> [tableTemp].[locationAC];
Can someone pleasae help.

 

[PHV]

And this ?
DELETE tableTemp.*
FROM tableTemp INNER JOIN tableTempNccIs1Compare
ON tableTemp.cellIdentity = tableTempNccIs1Compare.cellIdentity
AND tableTemp.locationAC <> tableTempNccIs1Compare.locationAC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AccessHelp123]

PH,

It gives me an error saying "Could not delete from the specified tables", the same error I got with the earlier query.

DELETE *
FROM tableTemp
WHERE cellIdentity In (SELECT [cellIdentity] FROM tableTempNccIs1Compare)
AND locationAC Not In (SELECT [locationAC] FROM tableTempNccIs1Compare);


I tried tried this and it seems like it is doing the job. But I am not really sure about this query. Any suggestions?

 

[PHV]

Perhaps this:
DELETE *
FROM tableTemp As T
WHERE cellIdentity In (SELECT cellIdentity FROM tableTempNccIs1Compare)
AND Not Exists (SELECT * FROM tableTempNccIs1Compare WHERE cellIdentity = T.cellIdentity AND locationAC = T.locationAC);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AccessHelp123]

This works. Appreciate your help PH.

 

[lgbatdw]

I am having a similar problem. I have no actual criteria, just want to delete all records in the "Sales Order Lineitems" table that match (on Sales Order Number) from the temp table "tempInvoiceArchive". When I try to run it I get the error "Operation must use an updatable query." My (Access 97) code is:

DELETE [Sales Order Lineitems].*
FROM [Sales Order Lineitems] INNER JOIN tempInvoiceArchive ON tempInvoiceArchive.[Sales Order Number] = [Sales Order Lineitems].[Sales Order Number];

I don't understand why it's not updatable & how to get around it.




Linda in MN

 

[PHV]

DELETE FROM [Sales Order Lineitems]
WHERE [Sales Order Number] In (SELECT [Sales Order Number] FROM tempInvoiceArchive)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lgbatdw]

Thanks PHV! This code worked like a charm. Sorry it took so long to reply as I was moved from this project to another for awhile. Now I'm back. Thanks again. It made my life easier.

Linda in MN