UPDATE table1 FROM table2 (using Sql Server Compact)

Question

I want to update rows in the [To] table, from rows in the [From] table, where the keys are equal. So this is what I'd do:

UPDATE [To]
SET    [To].[Name]      = [From].[Name],
       [To].[Size]      = [From].[Size],
       [To].[Something] = [From].[Something]
FROM   [From]
WHERE  [To].[Id] = [From].[Id];
go

Problem is, CE doesn't support FROM in the UPDATE command.

So, how do I change this syntax to work in CE?

possible duplicate of [How to do Sql Server CE table update from another table](http://stackoverflow.com/questions/1311236/how-to-do-sql-server-ce-table-update-from-another-table) — TTeeple, Aug 21 '15 at 14:05
@TTeeple No it's not a dupe as that answer doesn't work, and it's the same as Abhishek's answer, which doesn't work. — h bob, Aug 21 '15 at 14:11
How about this one? http://stackoverflow.com/questions/6933686/update-on-two-inner-joined-tables-in-sql-server-compact-4?rq=1 — TTeeple, Aug 21 '15 at 14:24
Not used CE, but could you turn it into two statements, a SELECT into variables and use those in the UPDATE? — Matt Allwood, Aug 21 '15 at 14:29
@MattAllwood Maybe I don't know. I'm hoping I can do it in one step, as I need to do this in an EF migrations `Sql()` call. Two steps would be to introduce another problem. — h bob, Aug 21 '15 at 14:32
@hbob I know, ugly as sin, but not allowing FROM is a major obstacle for anything else. It sounds as though it can cope with multiple SQL statements in one call, so the other requirements are quite low. Hope you don't need to do this too often though :-/ — Matt Allwood, Aug 21 '15 at 14:36
@hbob it's also the suggested solution to this similar question: http://stackoverflow.com/questions/6933686/update-on-two-inner-joined-tables-in-sql-server-compact-4?lq=1 — Matt Allwood, Aug 21 '15 at 14:37

score 1 · Answer 1 · answered Aug 21 '15 at 13:43

1

Try the below snippet (not tested) -

UPDATE [To]
SET    [To].[Name]      = [From].[Name],
       [To].[Size]      = [From].[Size],
       [To].[Something] = [From].[Something]
WHERE EXISTS (SELECT 1 FROM [FROM] WHERE [To].[Id] = [From].[Id])

answered Aug 21 '15 at 13:43

Abhishek

2,482
1
21
32

Nope it says `The column name is not valid. [ Node name (if any) = From,Column name = Name ]` – h bob Aug 21 '15 at 13:58
1

It seems that this can't be done in a single query when `UPDATE` needs to be done for **[TO]** from **[FROM]**. As per the suggestions the value needs to be stored in some variable and then use the variable to do the update. – Abhishek Aug 21 '15 at 14:19
how would you do that? – h bob Aug 21 '15 at 14:31
1

@hbob - the limitations of CE is forcing to make the update in two steps, either you choose to store the values matched between two tables and then do a simple update or to do a delete first based on the join conditions and then do a fresh insert. Problem is I don't have any CE system handy where I can test and let you know so all my solutions might seem vague. – Abhishek Aug 21 '15 at 17:16

score -1 · Answer 2 · answered Aug 21 '15 at 13:50

-1

Update to Set to.fld1 = from1.fld1 from table1 to inner join table from1 on to.id = from1.id

answered Aug 21 '15 at 13:50

benjamin moskovits

5,261
1
12
22

UPDATE table1 FROM table2 (using Sql Server Compact)

2 Answers2