Delete SQL query in Access not working as expected

Question

I have a table1 (PR_NOTES) where I'm trying to delete all records not found on table2 (DW_ECC_SAP_EBAN). I've tried several methods and this code is the closest (at least is not showing errors) that I've gotten to what I want:

DELETE *
FROM PR_NOTES
WHERE NOT EXISTS (SELECT 
DW_ECC_SAP_EBAN.BANFN, 
DW_ECC_SAP_EBAN.EKGRP, 
DW_ECC_SAP_EBAN.FRGZU, 
DW_ECC_SAP_EBAN.MENGE, 
DW_ECC_SAP_EBAN.BSMNG, 
DW_ECC_SAP_EBAN.LOEKZ, 
DW_ECC_SAP_EBAN.EBAKZ, 
DW_ECC_SAP_EBAN.PSTYP

FROM (DW_ECC_SAP_EBAN 
LEFT JOIN PR_NOTES
ON DW_ECC_SAP_EBAN.BANFN = PR_NOTES.BANFN));

To test this code, I created a fake record that I know is not found in table2:

The problem, is no record is ever deleted:

Thank you in advance for your help.

Error parenthesis hier?! FROM (DW_ECC_SAP_EBAN LEFT JOIN PR_NOTES . — user7941334
– user7941334, Commented May 18, 2017 at 15:21
Took them out (FROM DW_ECC_SAP_EBAN LEFT JOIN PR_NOTES ON DW_ECC_SAP_EBAN.BANFN = PR_NOTES.BANFN...) and unfortunately did not make a difference. — Pucho
– Pucho, Commented May 18, 2017 at 15:26

Raphael Cunha · Accepted Answer · 2017-05-18 15:24:42Z

1

The way I would do it is like this.

DELETE FROM PR_NOTES
WHERE PR_NOTES.BANFN NOT IN (SELECT DW_ECC_SAP_EBAN.BANFN FROM DW_ECC_SAP_EBAN)

I am selecting all of the BANFN from DW_ECC_SAP_EBAN and I'm telling PR_NOTES to delete records that are not on the list. So if PR_NOTES.BANFN is not in DW_ECC_SAP_EBAN.BANFN then it will get deleted from PR_NOTES

answered May 18, 2017 at 15:24

Raphael Cunha

1,1146 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pucho Over a year ago

With this query I'm getting an error " 'AggregateType' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long". I'm working with Access SQL.

Pucho Over a year ago

NVM, it works. I created a new query from scratch. Access for some reason keeps some sort of logic in the background that messes up the queries if you happen to modify them...

Collectives™ on Stack Overflow

Delete SQL query in Access not working as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related