delete from one if is doesn't exist in the other

[Kenln]

Hi,

I have a local and remote table.
What I'm trying to accomplish is deleting anything from the local table that doesn't exist in the remote table.

Of course the easy way is to delete the entire local table and re-insert. But I have data attached in other tables that I need to leave intact.

I thought I had a solution but it only works on a single record. If there are more that two records in the local table, I get an error.

    DELETE tbl_PayType_Local.* 
    FROM tbl_PayType_Local 
    WHERE 
    (
        tbl_PayType_Local.Pay_Type_ID= 
        (
            SELECT 
                tbl_PayType_Local.Pay_Type_ID 
            FROM 
                tbl_PayType 
                RIGHT JOIN tbl_PayType_Local 
                    ON tbl_PayType.Pay_Type_ID = tbl_PayType_Local.Pay_Type_ID 
            WHERE (((tbl_PayType.Pay_Type_ID) Is Null))
        )
    )

Any help or ideas would be appreciated.

Thank you,

 

[PNGBill]

If your field Pay_Type_ID is a Primary Key field or a field that does not allow duplicates, the name suggests, then you have only selected one field to delete.

If so, then change your criteria so you select all the records - best to test your delete query as a select query first - and then try.

Selecting all the records will not include such fields as a match.

 

[PNGBill]

Maybe I jumped to a conclusion as you are searching for Null in the field but as I say, if your select query returns more then one records then the delete version should do like wise.

Sometimes a field can appear to be Null but is not. Currency/Number fields can do this with small fractions that show on your form as an empty control but are not null

 

[Kenln]

Yes, the idea is to search for null in the remote table. If null then delete the records from the local table. It actually works greate if there is a single record that doesn't match, but... if two or more records don't match them I get an error saying it can only select a single record???

I can write vb and open a recordset, moving through the records and testing each against a DLookup. However, that is slow compared to a single query.

P.S. Yes the Pay_Type_ID is the key field for both tables and it's a text field. Being a key field it is set to no nulls.

 

[Kenln]

Don't I feel foolish.

    DELETE tbl_PayType_Local.* 
    FROM 
        tbl_PayType 
        RIGHT JOIN tbl_PayType_Local 
            ON tbl_PayType.Pay_Type_ID = tbl_PayType_Local.Pay_Type_ID 
        WHERE (((tbl_PayType.Pay_Type_ID) Is Null))

I believe this will work.