SQL Server Compact 4中的两个INNER JOINed表的更新 [英] UPDATE on two INNER JOINed tables in SQL Server Compact 4

问题描述

我正在尝试在sql server Compact Edition 4.0中的两个表之间更新值.我的sql如下所示:

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

也尝试过这个:

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

我遇到错误:

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

推荐答案

如果您查看联机丛书或其他参考，则会发现您无法在SQL Server CE中执行此操作.
-没有FROM子句
-没有相关的子查询

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

基本上，UPDATE语句可以引用的唯一数据是要更新的行中的数据.

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

我发现只有两种方法可以解决此问题:
1.客户端应用运行选择，然后触发一个或多个直接更新
2.插入新值，然后删除旧值

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

第一个是CE的工作原理(据我所知).例如，由于没有T-SQL，因此IF块和其他过程逻辑需要嵌入到应用程序中，而不是SQL.

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.

第二个模拟了UPDATE在触发器中的外观；删除和插入.并且只要您重新组织数据以使其成为可能，它就会非常有效.

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.

这两者都不是伟大的"，但是CE确实意味着您可以摆脱的最少".几乎就像是一个稍微有点浮夸的持久性引擎(以时髦的灵活格式将内容保存到磁盘)，而不是真正的数据库引擎.

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.

这篇关于SQL Server Compact 4中的两个INNER JOINed表的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！