SQL Server: delete rows which does not have a parent (based on 2 columns join)

Question

I am trying to delete entries from child table (accidents) which have not parent (location). If I understand correctly, I cannot have these rows in case I want to create a relationship between these two tables (one to one/many instead of one/zero to one/many which I have now). The foreign key is a combination of fk_veld5 and jaar. This is how I tried to tackle it:

DELETE FROM accidents 
WHERE FK_veld5 IN 
  (SELECT fk_veld5, Jaar_vkl FROM accidents
   EXCEPT SELECT FK_VELD5, JAAR FROM locations) 
 AND Jaar_vkl IN
  (SELECT fk_veld5, Jaar_vkl FROM accidents
   EXCEPT Select FK_VELD5, JAAR FROM locations)
GO

Selections in brackets contain fk_veld5 and Jaar_vkl from rows which I want to delete (I do not succeeded in selecting full rows which I want to delete).

After running the query above I am getting this error:

Msg 116, Level 16, State 1, Line 24 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I also tried to use EXISTS but it did not work.

I was trying to find a solution but every attempt ended with an error. Can you please answer me if what I want to do is possible? Or should I use LEFT JOIN to select all accidents which have the location and save them to new table which in the end will replace the old accidents table?

Answer 1

Your query is not correct, the sub select should only return one column when you use IN caluse

Try this

;with cte as
(
   SELECT fk_veld5, Jaar_vkl FROM accidents
   EXCEPT 
   Select FK_VELD5, JAAR FROM locations
)
DELETE a FROM accidents a
INNER JOIN CTE B on a.FK_veld5 = b.FK_veld5 and a.Jaar_vkl = b.Jaar_vkl