Question

我有一个表格,其中列有 Userid , Email 和 mobile 。 Email 没有无效值,但 mobile 在表格中各行都有空号。

我想用独有的 mobile 来更新每行的首行。我希望这有用, 但不确定。

UPDATE       userprofileplus
SET                mobile = 9199225533
WHERE        (UserId IN
                             (SELECT        TOP (1) UserId
                               FROM            userprofileplus AS userprofileplus_1
                               WHERE        (mobile IS NULL) AND (UserId = 5)))

我的目的是防止用户在表格上出现不必要的空格。我注意到一个问题,那就是 SQL 服务器的 Contaction < strong > 版不支持 TOP 关键字。我如何处理这个?

SQL SQL 服务器 CE 版本为 4. 0. 。我还注意到, < code> TOP 关键词得到了WebMatrix的完全支持, 但 VS 2010 SP1 拒绝SQL Server CE 4. 这里的 < code> TOP 关键词支持。这里发生了什么? 当在网络矩阵中运行时, 所有无移动的记录都会被更新, 忽略TOP 标准。我只需要更新第一个返回的行, 而不是全部更新。我也尝试过这个结果。

UPDATE       userprofileplus
SET                mobile = 9199225533
WHERE        (UserId IN
                             (SELECT        UserId
                               FROM            userprofileplus AS userprofileplus_1
                               WHERE        (mobile IS NULL) AND (UserId = 5) order by userid offset 1 rows))

来人啊保释我出去

Answer 1

......我终于得到了它经过几次逻辑的潜水。

UPDATE       userprofileplus
SET                mobile = 9199225533
WHERE        (mobile is null and userid = 5) and (Id IN
                             (SELECT        TOP (1) Id
                               FROM            userprofileplus AS userprofileplus_1
                               WHERE        (mobile IS NULL) AND (UserId = 5)))

I had to use my primary key column as a pivot to ensure a single row is returned from the subquery even though i was using a TOP criteria. But i m still curious why VS2010 sqlCE 4.0 does not support the TOP keyword but webmatrix does.Also, whats the reason why all columns where updated even after using the TOP keyword in the other queries?

友情链接