Geek asks and answers

MSSql (Compact) DELETE query with JOIN

I have this request. I want to remove all objects from AgentsResultLinks-Table that do not have an object reference in the Results-Table. I want a solution with a single request. 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 convert this query into a mssql vaid query for a compact database? Efficiency is very important.

+5
source share
2 answers
DELETE FROM AgentResultLinks 
where ResultID not in(select distinct ID from Results)
+4
source

Just remove .*fromAgentResultLinks.*

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

See syntax DELETE: DELETE (Transact-SQL)

See SQLFiddle Example

+9
source

More articles:


All Articles