SQL Server 2000：使用另一个表中的值更新一个表

发布于 2025-01-08 08:28:37 字数 980 浏览 0 评论 0原文

我有一个表单，显示系统列表及其当前状态。用户可以更改状态，并且该状态更改的日期存储在历史表中。用户还可以在状态更改时更改服务器的名称（例如，如果由于租用滚动而更换系统）。

历史表按systemname存储详细信息，因此如果表单上的系统名称发生更改，历史记录也需要更新（以及所有历史更改）。在表单上，有一个名为 originalsystemName 的隐藏字段，因此我们在保存表单时知道 systemname 是否匹配。

<input type='text' name='systemname'>
  <input type='text' name='originalSystemName'>
  <input type='text' name='status'><input type='submit' type='submit'>

我提出了以下查询，但收到错误（请参阅下面的查询）。

update SysHistory set  
SystemName = (
    select distinct t.systemname 
    from systemInfo_tmp t, SysHistory h 
    where t.systemname != t.originalSystemName
)    
where systemname in (
    select distinct t1.originalSystemName 
    from systemInfo_tmp t1, SysHistory h1 
    where t1.systemname != t1.originalSystemName
)

我收到的错误：

子查询返回超过 1 个值。当子查询跟在 =、!=、<、<=、>、>= 后面或子查询用作表达式时，这是不允许的。

原文

I have a form that displays a list of systems along with their current status. The user can change the status and the date of that status change is stored in a history table. The user can also change the name of the server as a status changes (for example, if a system is replaced due to a lease roll).

The history table stores the details by systemname so if the system name changes on the form, the history also needs to be updated (along with all the historical changes). On the form, there is a hidden field named originalsystemName so we know if the systemname matches or not when the form is saved.

<input type='text' name='systemname'>
  <input type='text' name='originalSystemName'>
  <input type='text' name='status'><input type='submit' type='submit'>

I came up with the following query but I'm getting an error (see below the query).

update SysHistory set  
SystemName = (
    select distinct t.systemname 
    from systemInfo_tmp t, SysHistory h 
    where t.systemname != t.originalSystemName
)    
where systemname in (
    select distinct t1.originalSystemName 
    from systemInfo_tmp t1, SysHistory h1 
    where t1.systemname != t1.originalSystemName
)

Error I'm receiving:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

分享到QQ

分享到微博

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

发布评论

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

白馒头 2025-01-15 08:28:37

有没有可能这就是你的意思？尽管除非这是单行表，否则似乎缺少 WHERE 子句。

UPDATE h
    SET SystemName = t.systemname
    FROM SysHistory AS h
    INNER JOIN systemInfo_tmp AS t
    ON h.systemname <> t.originalSystemName;

Is it possible that this is what you meant? Though unless this is a one-row table, there seems to be a WHERE clause missing.

UPDATE h
    SET SystemName = t.systemname
    FROM SysHistory AS h
    INNER JOIN systemInfo_tmp AS t
    ON h.systemname <> t.originalSystemName;

回复收藏 0 原文

心碎无痕… 2025-01-15 08:28:37

要使用另一个表中的值更新一个表，可以使用 join 轻松完成。

在此场景中，当 systemInfo_tmp 包含不存在的 systemName 时，sysHistory 表会连接到 systemInfo_tmp 表。 t 与原始版本匹配，并且 sysHistory 的 systemName 匹配。

update sh
set systemName = t.systemName
from
    sysHistory sh join
    systemInfo_tmp t on 
        t.originalSystemName = sh.systemName
        and t.originalSystemName != t.systemName

To update a table with values from another table, this is easily accomplished with a join.

In this scenario, the sysHistory table is joined to the systemInfo_tmp table when the systemInfo_tmp contains a systemName that doesn't match the original, and the sysHistory's systemName matches.

update sh
set systemName = t.systemName
from
    sysHistory sh join
    systemInfo_tmp t on 
        t.originalSystemName = sh.systemName
        and t.originalSystemName != t.systemName

回复收藏 0 原文

~没有更多了~