Convert Select query to Delete Query

[Michael Abrams]

I have a Select query that gives me the results I desire.

I would to take the results and delete them from the [WEEKLY AUDIT COUNT(12-16)] table.

Code: Select all

SELECT [WEEKLY AUDIT COUNT(12-16)].MEMNUM, [WEEKLY AUDIT COUNT(12-16)].ID, [WEEKLY AUDIT COUNT(12-16)].[TRANSACTION CODE], [WEEKLY AUDIT COUNT(12-16)].[RUN DATE], [WEEKLY AUDIT COUNT(12-16)].[SUBSCRIBER#], [WEEKLY AUDIT COUNT(12-16)].[MEM SUFFIX], [WEEKLY AUDIT COUNT(12-16)].[SUB SSN], [WEEKLY AUDIT COUNT(12-16)].[RELATION TO SUB], [WEEKLY AUDIT COUNT(12-16)].[LAST NAME], [WEEKLY AUDIT COUNT(12-16)].[FIRST NAME], [WEEKLY AUDIT COUNT(12-16)].MI, [WEEKLY AUDIT COUNT(12-16)].SEX, [WEEKLY AUDIT COUNT(12-16)].STREET, [WEEKLY AUDIT COUNT(12-16)].CITY, [WEEKLY AUDIT COUNT(12-16)].STATE, [WEEKLY AUDIT COUNT(12-16)].ZIP, [WEEKLY AUDIT COUNT(12-16)].PHONE, [WEEKLY AUDIT COUNT(12-16)].DOB, [WEEKLY AUDIT COUNT(12-16)].[PCP#], [WEEKLY AUDIT COUNT(12-16)].[EFF DATE W/PCP], [WEEKLY AUDIT COUNT(12-16)].[HEALTH PLAN CODE], [WEEKLY AUDIT COUNT(12-16)].[BENEFIT OPTION CODE], [WEEKLY AUDIT COUNT(12-16)].[COV EFF DATE], [WEEKLY AUDIT COUNT(12-16)].[COV TERM DATE], [WEEKLY AUDIT COUNT(12-16)].[EMP GROUP#], [WEEKLY AUDIT COUNT(12-16)].ERISA, [WEEKLY AUDIT COUNT(12-16)].PCPCONCAT
FROM [WEEKLY AUDIT COUNT(12-16)] LEFT JOIN [future dds delete] ON [WEEKLY AUDIT COUNT(12-16)].[MEMNUM] = [future dds delete].[MEMNUM]
WHERE ((([future dds delete].MEMNUM) Is Not Null));

Can this be done?

Thank you !

Michael

[HansV]

It depends on the indexes and the joins between the tables.
Try this first:

SELECT [WEEKLY AUDIT COUNT(12-16)].*
FROM [WEEKLY AUDIT COUNT(12-16)] LEFT JOIN [future dds delete] ON [WEEKLY AUDIT COUNT(12-16)].[MEMNUM] = [future dds delete].[MEMNUM]
WHERE [future dds delete].MEMNUM Is Not Null;

If that doesn't work, try

DELETE [WEEKLY AUDIT COUNT(12-16)].*
FROM [WEEKLY AUDIT COUNT(12-16)]
WHERE [WEEKLY AUDIT COUNT(12-16)].[MEMNUM] In (SELECT [MEMNUM] FROM [future dds delete]);

[Michael Abrams]

The second one worked perfectly !

Thank you Hans !

Michael