Using CTE on SQL Server Compact 3.5

Question

Hi Everyone,

This is my first post on stackoverflow, I hope one of many!

My question is this: I'm using CTE in a query to detect and remove duplicate records in a table. This query works just fine in SQL Server 2005 / 2008, but in Compact it throws an exception:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = WITH ]

This is my query:

SqlCeConnection con = new SqlCeConnection(ConfigurationManager.ConnectionStrings["ADSLConnectionString"].ConnectionString);
                SqlCeCommand command = new SqlCeCommand();

                command.Connection = con;
                command.CommandType = CommandType.Text;

                command.CommandText = "WITH Dublicates_CTE(Username, accountid)" +
                                      " AS" +
                                      " (" +
                                      "     SELECT UserName,min(accountid)" +
                                      "     FROM Accounts" +
                                      "     GROUP BY username" +
                                      "     HAVING Count(*) > 1" +
                                      " )" +
                                      "     DELETE FROM Accounts" +
                                      "     WHERE accountid IN (" +
                                      "         SELECT Accounts.accountid" +
                                      "         FROM Accounts" +
                                      "         INNER JOIN Dublicates_CTE" +
                                      "         ON Accounts.Username = Dublicates_CTE.Username" +
                                      "         AND Accounts.accountid <> Dublicates_CTE.accountid" +
                                      " ) ";

                con.Open();

                command.ExecuteNonQuery();

Am I missing something, or does CTE not work on SQL Server Compact?

Answer 1

some things are not supported by the mobile version CTE and store procs for example will not work on the mobile version. You could use the express version which is also free

Answer 2

Thanks... Maybe a workaround to detect and remove duplicates without using CTE?

Thanks

Answer 3

You can probably just nest the query, something like this (may have some syntax problems):

DELETE FROM Accounts
WHERE accountid IN (
    SELECT Accounts.accountid
    FROM Accounts
    INNER JOIN (
        SELECT UserName,min(accountid) accountid
        FROM Accounts
        GROUP BY username
        HAVING Count(*) > 1
    ) Dublicates_CTE
    ON Accounts.Username = Dublicates_CTE.Username
    AND Accounts.accountid <> Dublicates_CTE.accountid
)

Answer 4

Thank you very much, that works just fine! Syntax error free :)

Answer 5

For the future here is a good link Differences Between SQL Server Compact and SQL Server

Answer 6

Some proof regarding whether SQL Compact 3.5's TSQL subset can use Common Table Expressions:

Tested with Visual Studio 2010 and a new SQL Compact .sdf file.