SQL 更新 1 个表中的多行

Question

我有一个问题。顺便说一句，我正在使用 MS SQL Server Management Studio。

我有一个包含很多翻译的词典表。我需要将完整的描述从一个语言 ID 复制到另一个语言 ID。

示例如下。

LanguageID | Description
    2      | Some text
    2      | More text
    2      | Some more text
   10      | *needs to be replaced
   10      | *needs to be replaced
   10      | *needs to be replaced

结果一定是这样的:

LanguageID | Description
    2      | Some text
    2      | More text
    2      | Some more text
   10      | Some text
   10      | More text
   10      | Some more text

LanguageID 2和10的描述必须完全相同。

我当前的查询遇到错误:

update tblDictionary
set Description = (Select Description from tblDictionary where 
tblDictionary.LanguageID = 2)
where LanguageID = 10

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.

Answer 1

如果 LanguageID 10 的所有翻译必须与 languageID 2 完全相同，那么删除 ID 10 的所有翻译然后再次将它们插入会更容易。
像这样的事情

delete from tblDictionary where LanguageID = 10;

insert into tblDictionary (LanguageID, Description)
select 10, d.Description
from   tblDictionary d
where  d.LanguageID = 2

此方法还有一个优点，即如果 LanguageID = 10 的记录较少，则 LanguageID = 2 的记录较少，这将在同一过程中进行更正。

如果 tblDictionary 中的列数多于您需要修改的插入语句