UPDATE on two INNER JOINed tables in SQL Server Compact 4

Question

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 ]

Mannerheim

That's the error for the second version of your UPDATE, what is the error message for the first version? – Propylite

That's it, nothing else? If so I feel really bad for SQL Server Compact developers. – Propylite

Yes that's it. I just started using it but it seems no reason to use it. Anyway I managed to display and copy data by inner join. At least it suports JOIN... – Mannerheim

Have you looked at this question? #1311736 – Propylite

It seems okay in both versions, :S Compact edition sucks :/ – Marjoram

It seems that you may be returning multiple results in the SELECT (for the second one). You definitely want a WHERE clause on the UPDATE as well. – Nemesis

@narnian : No, it's just that you can't have SELECT or FROM in an UPDATE in SQL Server CE. It's a tiny runtime, designed to strip out virtually everything possible. I look at it as a challenge set by someone in MS - We can still do clever things with it, can You??? – Fabri

Answer 1

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.

Fabri

Answer 2

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

Jacki

There are 'In SQL' cursors in CE? I gotta find out about this, I thought that there was pretty much no T-SQL of any kind! – Fabri

Answer 3

You can do it this way and it works well

UPDATE USERS A

SET MOBILE = (SELECT B.MOBILE FROM NEW_MOBILE B WHERE A.USER = B.USER)

WHERE A.USER IN (SELECT C.USER FROM NEW_MOBILE C WHERE A.USER = C.USER)

Heckman

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach? – Pagurian

Answer 4

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..

Saintsimonianism