Delete query question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to delete records from one table if there are no matching records
on a second table. I am getting an error message that tells me to "Specify
the table containing the records you want to delete." I am not sure how to
change my SQL to do this. Can anyone offer advice? Here is my current SQL.

Thanks very much.
_________________________________________

DELETE [eBiz Template Allocation].ID, [eBiz Template Allocation].Employee,
[eBiz Template Allocation].[P-V Combo], [eBiz Template
Allocation].[Hours/Year], [eBiz Template Allocation].[Hours/Day], [eBiz
Template Allocation].PP1, [eBiz Template Allocation].PP2, [eBiz Template
Allocation].PP3, [eBiz Template Allocation].PP4, [eBiz Template
Allocation].PP5, [eBiz Template Allocation].PP6, [eBiz Template
Allocation].PP7, [eBiz Template Allocation].PP8, [eBiz Template
Allocation].PP9, [eBiz Template Allocation].PP10, [eBiz Template
Allocation].PP11, [eBiz Template Allocation].PP12, [eBiz Template
Allocation].PP13, [eBiz Template Allocation].PP14, [eBiz Template
Allocation].PP15, [eBiz Template Allocation].PP16, [eBiz Template
Allocation].PP17, [eBiz Template Allocation].PP18, [eBiz Template
Allocation].PP19, [eBiz Template Allocation].PP20, [eBiz Template
Allocation].PP21, [eBiz Template Allocation].PP22, [eBiz Template
Allocation].PP23, [eBiz Template Allocation].PP24, [eBiz Template
Allocation].PP25, [eBiz Template Allocation].PP26, [Employee Numbers].EmpName
FROM [eBiz Template Allocation] LEFT JOIN [Employee Numbers] ON [eBiz
Template Allocation].Employee = [Employee Numbers].EmpName
WHERE ((([Employee Numbers].EmpName) Is Null));
 
Try this:

DELETE DISTINCT [eBiz Template Allocation].*
FROM [eBiz Template Allocation] LEFT JOIN [Employee Numbers] ON [eBiz
Template Allocation].Employee = [Employee Numbers].EmpName
WHERE ((([Employee Numbers].EmpName) Is Null));


If that doesn't work, then this should do fine:

DELETE DISTINCT [eBiz Template Allocation].*
FROM [eBiz Template Allocation]
WHERE [eBiz Template Allocation].Employee NOT IN
(SELECT [Employee Numbers].EmpName
FROM [Employee Numbers]
WHERE [Employee Numbers].EmpName Is Null);
 
REMOVE all but one field in the DELETE Clause. That one field should be in
the table you want to delete records from.

So if you want to delete the records in eBiz Template Allocation, you should
have something like

DELETE DistinctRow [eBiz Template Allocation].ID
FROM [eBiz Template Allocation] LEFT JOIN [Employee Numbers]
ON [eBiz Template Allocation].Employee = [Employee Numbers].EmpName
WHERE ((([Employee Numbers].EmpName) Is Null));
 
Thanks to all of you who responded! I will try both of these methods so I
can become familiar with them.
--
Ann Scharpf


John Spencer said:
REMOVE all but one field in the DELETE Clause. That one field should be in
the table you want to delete records from.

So if you want to delete the records in eBiz Template Allocation, you should
have something like

DELETE DistinctRow [eBiz Template Allocation].ID
FROM [eBiz Template Allocation] LEFT JOIN [Employee Numbers]
ON [eBiz Template Allocation].Employee = [Employee Numbers].EmpName
WHERE ((([Employee Numbers].EmpName) Is Null));

Ann Scharpf said:
I am trying to delete records from one table if there are no matching
records
on a second table. I am getting an error message that tells me to
"Specify
the table containing the records you want to delete." I am not sure how
to
change my SQL to do this. Can anyone offer advice? Here is my current
SQL.

Thanks very much.
_________________________________________

DELETE [eBiz Template Allocation].ID, [eBiz Template Allocation].Employee,
[eBiz Template Allocation].[P-V Combo], [eBiz Template
Allocation].[Hours/Year], [eBiz Template Allocation].[Hours/Day], [eBiz
Template Allocation].PP1, [eBiz Template Allocation].PP2, [eBiz Template
Allocation].PP3, [eBiz Template Allocation].PP4, [eBiz Template
Allocation].PP5, [eBiz Template Allocation].PP6, [eBiz Template
Allocation].PP7, [eBiz Template Allocation].PP8, [eBiz Template
Allocation].PP9, [eBiz Template Allocation].PP10, [eBiz Template
Allocation].PP11, [eBiz Template Allocation].PP12, [eBiz Template
Allocation].PP13, [eBiz Template Allocation].PP14, [eBiz Template
Allocation].PP15, [eBiz Template Allocation].PP16, [eBiz Template
Allocation].PP17, [eBiz Template Allocation].PP18, [eBiz Template
Allocation].PP19, [eBiz Template Allocation].PP20, [eBiz Template
Allocation].PP21, [eBiz Template Allocation].PP22, [eBiz Template
Allocation].PP23, [eBiz Template Allocation].PP24, [eBiz Template
Allocation].PP25, [eBiz Template Allocation].PP26, [Employee
Numbers].EmpName
FROM [eBiz Template Allocation] LEFT JOIN [Employee Numbers] ON [eBiz
Template Allocation].Employee = [Employee Numbers].EmpName
WHERE ((([Employee Numbers].EmpName) Is Null));
Click to expand...
Click to expand...
 
Back
Top