VBA sql Delete statement

[ismailr]

Hello,

Request quick Help.

I have two tables;
TableA (fields CoCode, EmpDept) and
TableB(Fields CoCode, EmpDept, ReportingMapping)
Both Tables are linked through CoCode and EmpDept

Need help to form delete script to delete records from TableA, only those records which does not exist in TableB

Thanks
Ismail

 

[Ranman256]

you cant delete by joining tables (too easy, thanks Microsoft)
instead you must do it like a FIND DUPLICATES query.
use the query wizard to make a find dupicates query, then look at the query,
in the key field is criteria: IN (select fld from table)

do this in your delete query. (a query in a query field)
delete * from table1 where [index] in (select [index] from table2)

hard to explain , but easy to see in the Find Duplicates query.

 

[ismailr]

Thanks for the quick response.

What i understood from The FND Duplicate query wizard can analyse single table and find the duplicate.

I have two tables which are connected through two fields. I don't think the FND duplicate query wizard provide me the sql.

thanks
Ismail

 

[ismailr]

Hi,
I managed to the expected result from the below query. Please validate;

DELETE * from TableA As A
where A. CoCode & A. EmpDept not in (
select B. CoCode & B. EmpDept from TableABAs B
)

Thanks
Ismail