根据另一个表更新表

发布于 2024-11-07 14:35:07 字数 396 浏览 1 评论 0原文

我正在尝试根据另一个表中的另一列更新表中的列。

UPDATE eval e
   SET rank = (SELECT p.desc
                 FROM Position p
                WHERE p.id = e.faculty 
                  AND p.date >= '2011-05-20'
              )

p.id 和 e.faculty 对应。如果 id 相同，我想用 p.desc 更新排名。（e.faculty 和 p.id）

任何帮助都会很棒！ :)

原文

I'm trying to update a column in a table based on another column in another table.

UPDATE eval e
   SET rank = (SELECT p.desc
                 FROM Position p
                WHERE p.id = e.faculty 
                  AND p.date >= '2011-05-20'
              )

p.id and e.faculty correspond. I want to update rank with p.desc if the id's are the same. (e.faculty and p.id)

Any help will be great! :)

分享到QQ

分享到微博

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

发布评论

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

红玫瑰 2024-11-14 14:35:07

对于 SQL Server 尝试此操作：

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.Position p
WHERE p.id = eval.faculty and p.date >= '2011-05-20'

或者如果您需要基表上的别名（无论出于何种原因），您需要执行以下操作：

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.eval e
INNER JOIN dbo.Position p ON p.id = e.faculty 
WHERE p.date >= '2011-05-20'

Try this for SQL Server:

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.Position p
WHERE p.id = eval.faculty and p.date >= '2011-05-20'

or if you need an alias on the base table (for whatever reason), you need to do this:

UPDATE dbo.eval 
SET rank = p.desc
FROM dbo.eval e
INNER JOIN dbo.Position p ON p.id = e.faculty 
WHERE p.date >= '2011-05-20'

回复收藏 0 原文

放手` 2024-11-14 14:35:07

您需要一个 WHERE 子句形式的限制；如果您使用EXISTS，您可以基于标量子查询，例如

UPDATE eval
   SET rank = (
               SELECT p.desc
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              )
 WHERE EXISTS (
               SELECT *
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              );

注意上面的目标是基表eval上的UPDATE而不是相关名称e。当您从关系分配的角度考虑 SQL UPDATE 时，这更有意义，即您不想分配给 e 因为它（与基表不同）将超出范围！

You need a restriction in the form of a WHERE clause; if you use EXISTS you can based it on you scalar subquery e.g.

UPDATE eval
   SET rank = (
               SELECT p.desc
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              )
 WHERE EXISTS (
               SELECT *
                 FROM Position p
                WHERE p.id = eval.faculty 
                      AND p.date >= '2011-05-20'
              );

Note the above targets the UPDATE on the base table eval rather than the correlation name e. This makes a lot more sense when you think of an SQL UPDATE in terms of relational assignment i.e. you don't want to assign to e because it (unlike the base table) will go out of scope!

回复收藏 0 原文

~没有更多了~