MSSql (Compact) DELETE-Query with JOIN

Question

I have this query. I want to delete all entities from AgentsResultLinks-Table, that don't have a link to a entity in Results-Table. I want a solution with one single query. I got an error caused by '*'.

DELETE AgentResultLinks.*
FROM AgentResultLinks LEFT JOIN Results 
ON AgentResultLinks.ResultID = Results.ID
WHERE Results.ID IS NULL

Can someone help me to convert this query in a vaid mssql query for compact database ? The Performance is very important.

Solution 1

DELETE FROM AgentResultLinks 
where ResultID not in(select distinct ID from Results)

Solution 2

Just remove .* from AgentResultLinks.*

DELETE Agent
FROM AgentResultLinks Agent 
LEFT JOIN Results R
       ON Agent.ResultID = R.ID
WHERE R.ID IS NULL;

See DELETE syntax: DELETE (Transact-SQL)

See SQLFiddle Example