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.

[This link explains the answer why you can't update (or delete maybe) a table that has join on it on SQL Server CE.](http://stackoverflow.com/a/6934448/491243) — John Woo, Sep 21 '12 at 09:16

Himanshu · Answer 1 · 2012-09-21T08:03:53.823

9

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

edited Sep 21 '12 at 08:03

answered Sep 21 '12 at 07:27

Himanshu

31,810
31
111
133

@Gepro Give alias to table and use it. Updated the answer. – Himanshu Sep 21 '12 at 07:38
1

There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = FROM ] – Gepro Sep 21 '12 at 07:58
Are you using the same query? Look at [this SQLFiddle example](http://sqlfiddle.com/#!3/2e30b/1). I am not getting any error. – Himanshu Sep 21 '12 at 07:59
4

[This link explains the answer why you can't update (or delete maybe) a table that has join on it on SQL Server CE.](http://stackoverflow.com/a/6934448/491243) – John Woo Sep 21 '12 at 09:16

score 4 · Accepted Answer · edited Sep 21 '12 at 08:31

4

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

edited Sep 21 '12 at 08:31

Himanshu

31,810
31
111
133

answered Sep 21 '12 at 08:06

Vikram Jain

5,498
1
19
31

1

this query i had tried before i wrote this post. it's too slow. but it works. – Gepro Sep 21 '12 at 08:18
AgentResultsLinks > 500000 and Results > 20000 – Gepro Sep 21 '12 at 08:59

MSSql (Compact) DELETE-Query with JOIN

2 Answers2

Linked