sql-server - 将列值从一个表复制到另一个表

Question

我正在尝试创建一个 T-SQL 查询几个小时。谷歌和论坛搜索没有帮助，所以我希望有人能帮助我解决这个问题。

有两个表，Change 和Journal。 Change 中的每一行在 Journal 中都有 5 行相关。

我想做的是将Journal中特定相关行的列值(CreatedDateTime)复制到AuthorizationDate列中更改。这应该对 Change 中的所有行完成。

这是我到目前为止所取得的成就:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = 
    ( SELECT Journal.CreatedDateTime 
      FROM Journal 
        JOIN Change 
          ON Journal.Parentlink_RecID = Change.RecID 
      WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' 
        AND Change.RecID = Journal.ParentLink_RecID
    )

不幸的是，我收到以下错误消息:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

我想我确实通过 WHERE 子句 (ChangeNotes_Subject = '...') 将结果限制为 1。我做错了什么？

Answer 1

我认为这会起作用:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = 
    ( SELECT Journal.CreatedDateTime 
      FROM Journal 
      WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' 
        AND Change.RecID = Journal.ParentLink_RecID
    ) ;

但使用 JOIN 更好 - 避免任何不需要的更新。方法如下:

UPDATE Change 
SET Change.ap_ITSM_MDP_AuthorizationDate = Journal.CreatedDateTime 
FROM Journal 
  JOIN Change 
    ON Change.RecID = Journal.ParentLink_RecID
WHERE ITSM_ChangeNotes_Subject = 'Status changed to: Authorized' ;