CODEWITHSUNDEEP
c#sql-server
frankclaassens
frankclaassens

Reputation: 1930

Using CTE on SQL Server Compact 3.5

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?

Upvotes: 2

Views: 3014

Answers (4)

p.campbell
p.campbell

Reputation: 100607

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

alt text

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

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135121

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

Upvotes: 1

Lucero
Lucero

Reputation: 60266

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
)

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135121

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

Upvotes: 1

Related Questions