SQL Delete Statement (Where <Field> Not In)

How 开发者_开发知识库can I delete a specific row on a table only if its data is not used by another table anymore?

Lets say I have a [schedule] table and [scheduletype] table. If a specific [scheduletype] is not found in [schedule].[type] field of the [schedule] table then it will be removed from the [scheduletype] table.

BTW I am using SQL Server 2005.

---

Try this (assuming the relevant field is called id in schedule):

DELETE FROM scheduletype WHERE id NOT IN (select a.type FROM schedule a)

---

Well, first is you should probably set up a foreign key constraint to ensure deletes can't happen if the tables are logically related. Read more here: http://msdn.microsoft.com/en-us/library/ms175464(v=SQL.90).aspx

For the delete, you'd do this:

delete scheduletype 
where type not in (select type from schedule)