Substitution for DELETE INNER JOIN in SQL Server CE

Question

I have some SQL Server queries that I need to convert into SQL Server CE. I am facing some troubles to say the least. This is what I am working with:

DELETE FROM exSetData 
WHERE exSetData 
INNER JOIN Exercise ON exSetData.exName = Exercise.Name 
WHERE Exercise.Day = @name; 

I began by attempting to use AND EXISTS in SQL Server CE, however I do not believe it is capable of performing the task I need and clearly I am using it incorrectly.

DELETE FROM exSetData 
WHERE EXISTS (Exercise.Name = exSetData.exName AND Exercise.Day = @name)

Syntax error.

How might one do this same query in SQL Server CE?

Are there any other commands you would advise me to look into? I have been using Microsoft documentation however it is not as thorough in DELETE (in my opinion).

I'm using SQL Server CE 4.0 by the way.

Thanks

Answer 1

This should work using IN:

DELETE FROM exSetData
WHERE exName IN (
    SELECT Name
    FROM Exercise
    WHERE Day = @name )

SQL Fiddle Demo

While I can't test in CE, I think your original join syntax is off and should look like this:

DELETE a
FROM exSetData a
  JOIN exercise e on a.exName = e.Name
WHERE e.Day = @name