sql server 2005“更新自”询问

发布于 2024-11-07 13:38:21 字数 700 浏览 0 评论 0原文

我正在尝试根据另一个表中的用户 ID 更新一个表。我遇到过 Update from 语法，但我很难正确编写查询。

下面的代码应该向您展示我正在尝试做什么。当我运行它时，有 0 行受到影响。

    update jared_test
       set user_count  = 1
      from new_user nuj
inner join (select us.userID
              from users us
             where us.email = '[email protected]') u on nuj.userid = u.userid

/********编辑*******************\

我发现我的游标循环存在问题，导致其无法工作，所以这确实有效。不过，在这种情况下，我很感兴趣，在优化方面，where 是否比 from 更好。

原文

I'm trying to update a table based upon the user id from another table. I've come across the Update from syntax but I'm struggling to write my query correctly.

The below code should show you what I'm attempting to do. When i run it i get 0 rows affected.

    update jared_test
       set user_count  = 1
      from new_user nuj
inner join (select us.userID
              from users us
             where us.email = '[email protected]') u on nuj.userid = u.userid

/********EDIT*******************\

I discovered there was a problem with my Cursor loop that was preventing this from working, so this does actually work. However I'd be interested if a where is better than a from in this instance for optimisations.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

十年九夏 2024-11-14 13:38:21

update jared_test
set user_count = 1
where userid = 
  (select userID from users where email = '[email protected]')

试试这个

update jared_test
set user_count = 1
where userid = 
  (select userID from users where email = '[email protected]')

try this

回复收藏 0 原文

江城子 2024-11-14 13:38:21

我并不是 100% 明白为什么其他解决方案使用子选择，它的执行速度通常比常规连接慢。虽然 taos subselect 本质上是一个常规连接，只是写得有趣。

update aliasName
set aliasName.user_count =1 
from new_user aliasName
inner join users u on aliasName.userid = u.userid
where email = '[email protected]'

I'm not 100% on why the other solutions are using a subselect which will perform slower than a regular join most often. Though taos subselect is essentially a regular join just written interestingly.

update aliasName
set aliasName.user_count =1 
from new_user aliasName
inner join users u on aliasName.userid = u.userid
where email = '[email protected]'

回复收藏 0 原文

围归者 2024-11-14 13:38:21

您似乎没有在表“jared_test”和您选择的两个表“new_user/nuj”和“users/us”之间建立任何关系。

你是这个意思吗？

update nuj
set user_count  = 1
from new_user nuj
inner join (select us.userID
            from users us
            where us.email = '[email protected]') u on nuj.userid = u.userid

（如果是这样，@Devan 建议的标准更新会更有意义）

You don't seem to be establishing any relationship between the table "jared_test" and the two tables that you are selecting by, "new_user/nuj" and "users/us".

Did you mean this?

update nuj
set user_count  = 1
from new_user nuj
inner join (select us.userID
            from users us
            where us.email = '[email protected]') u on nuj.userid = u.userid

(if so, a standard update as @Devan suggested would make more sense)

回复收藏 0 原文

~没有更多了~