Delete query assistance

[Michael Abrams]

Good day all - hope everyone is staying safe.

I am trying to delete records from table BLUE that match records in table 178

I receive the error message “specify the table contain the records you want to delete.

Doesn’t the FROM clause specify the table BLUE?

Code: Select all

DELETE [BLUE].[HMO Provider # Field], [BLUE].[PCP Name], [178].[HMO Provider # Field]
FROM [BLUE] LEFT JOIN 178 ON [BLUE].[HMO Provider # Field] = [178].[HMO Provider # Field]
WHERE ((([178].[HMO Provider # Field]) Is Not Null));

What did i miss?

Thank you and wishing everyone to be healthy.

Michael

[HansV]

Does this work?

Code: Select all

DELETE [BLUE].*
FROM [BLUE] LEFT JOIN 178 ON [BLUE].[HMO Provider # Field] = [178].[HMO Provider # Field]
WHERE [178].[HMO Provider # Field] Is Not Null;

If not, try

Code: Select all

DELETE *
FROM [BLUE]
WHERE [HMO Provider # Field] In (SELECT [HMO Provider # Field] FROM [178])

[Michael Abrams]

Thank you HansV. The second one worked !

Much appreciated.

Michael