Search code examples
sqlt-sqlsql-server-cesql-server-ce-4

UPDATE on two INNER JOINed tables in SQL Server Compact 4

I'm trying to update values between two tables in sql server compact edition 4.0. My sql is like below:

UPDATE ei SET ei.EstateID=e.EstateID FROM EstateImages ei
    INNER JOIN Estates e ON e.TempKey=ei.TempKey

Also tried this:

UPDATE EstateImages SET EstateID = 
    (SELECT EstateID FROM Estates WHERE TempKey = EstateImages.TempKey)

I'm having error:

There was an error parsing the query.
[ Token line number = 1, Token line offset = 37, Token error = SELECT ]
Solution

  • If you check Books Online or other references, you'll find that you can't do this in SQL Server CE.
    - No FROM clause
    - No correlated sub-queries

    Basically, the only data an UPDATE statement can reference is the data in the row being updated.

    There are only two methods that I have found to get around this:
    1. Client app runs a select, then fire off one or more direct updates
    2. INSERT the new values, then DELETE the old values

    The first is pretty much how CE is (as far as I know) intended to work. There is no T-SQL, for example, so IF blocks and other procedural logic needs to be embedded in the application, not the SQL.

    The second mimic what an UPDATE looks like in a trigger; A delete and an insert. And provided you re-structure your data to make this possible, it's quite effective.

    Neither are 'great', but then CE really is meant to be the "least you can get away with". It's almost like it's more a slightly flashy persistance engine (save stuff to disk in a funk flexible format), and less a real database engine.

    Once you get used to it's limitations, and the ways to work around them, however, it can be pretty useful. For specific tasks.