Detete record based on another table 1

[Barra44]

I have 2 tables

Table 1
Leavetbl - field tempdate

Table 2
Holidays - field holidate

tables linked one to many holidate-tempdate


I wish to delete records from table 1 where leavedate = holidate

This is what I have written but when I run it i am prompted to enter a value for holidate.


DELETE [Leavetbl].tempdate
FROM Leavetbl
WHERE (([Leavetbl].tempdate)=(Holidays.holidate));



Thanks in advance

 

[Genimuse]

I'm no pro, but you do need to reference the Holiday table in your table list, along these lines:

DELETE [Leavetbl].tempdate
FROM Leavetbl[COLOR=blue], Holidays[/color]
WHERE (([Leavetbl].tempdate)=(Holidays.holidate));

No guarantees that it will work, but it's more correct, anyway. ;-)

 

[Barra44]

I added Holidays in the from line and ran it, now it asks me to "specify the table containig the record you want to delete

 

[Genimuse]

Perhaps

DELETE Leavetbl.* 
FROM Leavetbl
INNER JOIN Holidays 
ON Leavetbl.tempdate = Holidays.holidate
WHERE Leavetbl.tempdate = Holidays.holidate

 

[Genimuse]

Actually you probably don't need the WHERE.

 

[Barra44]

Thankyou Genimuse that worked a treat

 

[gbrian]

When I try to do this same thing- it says "Cannot delete from specified tables."

Well why not!?

 

[eti3nne]

Quote: When I try to do this same thing- it says "Cannot delete from specified tables."

Well why not!?

I am having the same problem also. Here is my code...

DELETE [Oxygen].*
FROM [Oxygen] INNER JOIN AdminCodes ON [Oxygen].[GP Code] = AdminCodes.[GP ADMIN CODE];

Can anyone suggest a solution please?

 

[PHV]

DELETE FROM [Oxygen]
WHERE [GP Code] In (SELECT [GP ADMIN CODE] FROM AdminCodes);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886