Delete rows where there is no constraint

Question

I have a table that I have managed to duplicate a lot of rows by accident. The id column is auto increment and has a foreign key constraint. What I would like to do is delete all rows where there is not a constraint that is active. Is this possible?

EDIT

This query gives me a list of the IDS of the records that need deleting, is there a nice way of editing this to delete after?

SELECT WHL_LEVEL_ID
FROM [RMIS].[dbo].[WHL_LEVEL_DETAIL]
except
select WHL_LEVEL_ID
from [RMIS].[dbo].[WHL_SKU_LOCATION]

With the query itself! I don't understand how to expand a delete from query with a where clause that checks if there is data linked to the key — dave, Sep 22 '14 at 10:23
possible duplicate of [Delete all records that have no foreign key constraints](http://stackoverflow.com/questions/2785271/delete-all-records-that-have-no-foreign-key-constraints) — bummi, Sep 22 '14 at 10:30

score 0 · Accepted Answer · answered Sep 22 '14 at 10:44

So you already have the WHL_LEVEL_IDs of the rows which can be deleted. The typical solution would be something like

delete from [RMIS].[dbo].[WHL_LEVEL_DETAIL]
where WHL_LEVEL_ID in (
    SELECT WHL_LEVEL_ID
    FROM [RMIS].[dbo].[WHL_LEVEL_DETAIL]
    except
    select WHL_LEVEL_ID
    from [RMIS].[dbo].[WHL_SKU_LOCATION]
)

Though I am not 100% sure whether SQLserver supports where .. in (select and if it uses this exact syntax.

Delete rows where there is no constraint

1 Answers1