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 = EstateImag开发者_开发问答es.TempKey)

I'm having error:

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

---

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.

---

You have a few options to do it staying within SQL:

1. Insert new values and delete old values (assuming no primary key)
2. Insert the new values into a temp table and then delete the old values and then insert them back (you might need to turn identity insert on for this, however this will break if you have foreign key constraints)
3. Use a cursor and dynamic SQL to update the rows one by one

Edit: no idea what I was thinking for #3, no idea what I was thinking... "In SQL Server Compact 4.0, it is possible to request a cursor only by using API functions." From http://msdn.microsoft.com/en-us/library/ms172364(SQL.110).aspx

---

I had the same problem with DELETE operation, but I got it sorted by this way.

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

Hope it's useful for you whilst in your UPDATE operation.... Food for thought may be..