Deletion Query to delete records in one table dependent on another table

[Riordan]

Hi,

I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).

At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on mulitple contracts).

DELETE *
FROM M0070
WHERE EXISTS
(SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week], M0070.[Rate Letter], M0070.[Hourly Rate], M0070.[Hours per week]
FROM M0070, [Amendments History File]
WHERE (((M0070.[Contract No]) Like [Amendments History File].[Post]) AND ((M0070.[Employee No]) Like [Amendments History File].[Employee Payroll Number] And (M0070.[Employee No]) Like [Amendments History File].[Employee Payroll Number]) AND (([Amendments History File].Effective_Date) Between [startdate] And [enddate]) AND (([Amendments History File].[Reason _for _Change])="D"));


Many Thanks in anticipation

Rachel

 

[CJ_London]

you need to alias your subquery, for exists, you don't need all the fields returned

DELETE *
FROM M0070
WHERE EXISTS
(SELECT *
FROM M0070 [COLOR=red]AS T[/COLOR], [Amendments History File]
WHERE ((([COLOR=red]T[/COLOR].[Contract No]) Like [Amendments History File].[Post]) AND (([COLOR=red]T[/COLOR].[Employee No]) Like [Amendments History File].[Employee Payroll Number] And ([COLOR=red]T[/COLOR].[Employee No]) Like [Amendments History File].[Employee Payroll Number]) AND (([Amendments History File].Effective_Date) Between [startdate] And [enddate]) AND (([Amendments History File].[Reason _for _Change])="D"));

 

[Riordan]

Sorry that doesn't work - it now prompts for the payroll number which it should be picking up from the amendments history file.

 

[CJ_London]

well if it is asking for something from the amendments history file, what has that got to do with aliasing M0070? Sound more like Employee Payroll Number doesn't exist in the amendments history file.

I did notice you are making the criteria twice for some reason and don't understand why your using a Cartesian query rather than a join, perhaps it is something to do with that