Delete an entries that are not in

[kikeman]

Hi,

I have two tables: TableA and TableB they both have a column called "OrderNo" (same type and info, of course), I would like to delete those entries in TableB that ARE NOT in TableA according to the "OrderNo".

"OrderNo" is the Primary Key in TableA, but it is allow to have duplicates in TableB of "OrderNo"

It is to say, I would have in TableB "OrderNo" values only if they exist in TableA, now I want to avoid to have "OrderNo" in TableB that does no exist in TableA.

What would SQL command to do this?
I am using OleDB in C#.

Thanks,
Enrique.

 

[ozinm]

try:

DELETE FROM TableB WHERE OrderNo NOT IN (SELECT DISTINCT OrderNo FROM TableA);

 

[kikeman]

If I want to view this info from Access2003 what should I need to do?
(Entries that are in TableB but not in TableA according to the "OrderNo")

 

[ozinm]

If I want to view this info from Access2003 what should I need to do?
(Entries that are in TableB but not in TableA according to the "OrderNo")

This should list the rows, rather than deleting them.

SELECT * FROM TableB WHERE OrderNo NOT IN (SELECT DISTINCT OrderNo FROM TableA);