在单个update语句中更新多条记录

Question

在过去的几个小时里，我一直在这样做，我最终还是想寻求帮助。我正在尝试用另一个表中的多个记录来更新一个表中的多个不同记录。下面是设置：

表x ( QuestID，SectionID，Questxt) PK= QuestID

表y ( QuestAGID，QuestID，AGID) PK =QuestAGID

对于某些上下文，一个表有问题，另一个表有问题答案。问题与一个部分相匹配。每个问题的答案最多匹配一个问题。通过复制问答表中的记录，我现在一个问题的问答表中有2条相同的记录。我希望能够获取这些重复的记录，并用我创建的新问题替换问题标识符。我在想出一条update语句时遇到了麻烦，该语句能够对多条记录进行一对一的替换。

这就是我到目前为止想出的：

declare @a as int
select @a = Count(x.sectionid) from x
where Sectionid=6584

update y
set QuestId = (select QuestId, ROW_NUMBER() 
    OVER (ORDER BY questid) AS Row2
    from x 
    where SectionId=7700)
from y
join x on y.QuestId=x.QuestId
where x.Questid IN 
( 
select top (@a) y.QuestId, ROW_NUMBER() 
    OVER (ORDER BY y.Questid) as Row1
    from y
inner join x
on y.QuestId=x.QuestId
where x.SectionId=6584
)

任何帮助都将不胜感激。

谢谢!

编辑:以下是两个子查询的结果示例：

查询1

select QuestionId, ROW_NUMBER() 
    OVER (ORDER BY questionid)
    from Questions 
    where SectionId=7700

结果：

QuestionId     column2    
-------------  ---------- 
31254          1          
31255          2  

查询2

declare @a as int
select @a = Count(Questions.sectionid) from Questions
where Sectionid=6584

select top (@a) Questions_AnswerGroup.Questionid, ROW_NUMBER() 
OVER (ORDER BY Questions_AnswerGroup.Questionid) 
from Questions_AnswerGroup
inner join Questions
on Questions_AnswerGroup.QuestionId=Questions.QuestionId
where Questions.SectionId=6584

结果：

Questionid     column2    
-------------  ---------- 
24990          1          
24991          2  

第二次编辑:我将尽可能地澄清这一点，以帮助大家理解，并感谢大家的关注。

因此，这是我的第一步，我复制了基于区号的Question_AnswerGroup记录。

insert into Questions_AnswerGroup (Questions.QuestionId,
Questions_AnswerGroup.AnswerGroupId,Questions_AnswerGroup.ControlTypeId,
         Questions_AnswerGroup.IsRequired,Questions_AnswerGroup.Rank,Questions_AnswerGroup.ColumnCou    nt,
Questions_AnswerGroup.EmailRecipient,Questions_AnswerGroup.FilterName,Questions_AnswerGroup    .ResponseName,
Questions_AnswerGroup.IsHeader,Questions_AnswerGroup.IsVariable,Questions_AnswerGroup.MaxVa    lue,
Questions_AnswerGroup.DefaultValue,Questions_AnswerGroup.CharCount,Questions_AnswerGroup.Ad    ditionalInfo,
Questions_AnswerGroup.ConditionsId,Questions_AnswerGroup.InitVisible)
SELECT Questions_AnswerGroup.QuestionId,Questions_AnswerGroup.AnswerGroupId,
Questions_AnswerGroup.ControlTypeId,Questions_AnswerGroup.IsRequired,Questions_AnswerGroup.    Rank,
Questions_AnswerGroup.ColumnCount,Questions_AnswerGroup.EmailRecipient,Questions_AnswerGrou    p.FilterName,
Questions_AnswerGroup.ResponseName,Questions_AnswerGroup.IsHeader,Questions_AnswerGroup.IsV    ariable,
Questions_AnswerGroup.MaxValue,Questions_AnswerGroup.DefaultValue,Questions_AnswerGroup.Cha    rCount,
Questions_AnswerGroup.AdditionalInfo,Questions_AnswerGroup.ConditionsId,Questions_AnswerGro    up.InitVisible
FROM Questions_AnswerGroup
inner join Questions
on Questions_AnswerGroup.QuestionId=Questions.QuestionId
where Questions.SectionId=6584

这给了我一个在Question_AnswerGroup表中看起来像这样的输出：

QuestionAnswerGroupId   QuestionId
17438                      24990
17439                      24991
17444                      24990
17445                      24991

目前，两个QuestionAnswerGroupIds (原始版本和我刚刚复制的版本)都与QuestionIds关联，它们都属于第6584节。我现在需要获取我创建的两个QuestionAnswerGroupIds，并将它们与第7700节中没有答案的两个问题关联起来。

Select questionid from Questions where sectionid=7700

这将导致以下结果：

questionid
31254
31255

因此，在结论中，我需要将上述存在于questions表中的两个questionID(来自第7700节)放在我上面创建的副本的位置。

编辑3:重复的问题是这样创建的：

insert into Questions(Questions.Rank,Questions.QuestionText,Questions.IsActive,Sections.SectionId,Questions.ShowNumber,Questions.IsTextOnly,Questions.ContextHelp,Questions.ConditionsId,Questions.InitVisible)
select Questions.Rank,Questions.QuestionText,Questions.IsActive,(Max(Sections.SectionId)),Questions.ShowNumber,Questions.IsTextOnly,Questions.ContextHelp,Questions.ConditionsId,Questions.InitVisible
from Questions, Sections
where Questions.SectionId=6584
Group by      Questions.Rank,Questions.QuestionText,Questions.IsActive,Questions.ShowNumber,Questions.IsTextOnly,Questions.ContextHelp,Questions.ConditionsId,Questions.InitVisible

Answer 1

A猜测，但我认为关于你想要什么，还有一些悬而未决的问题。

;WITH src AS 
(
  SELECT QuestionId, r = ROW_NUMBER() OVER (ORDER BY QuestionId)
  FROM dbo.Questions
  WHERE SectionId = 7700
), trg AS
(
  SELECT QuestionId, r = ROW_NUMBER() OVER (ORDER BY QuestionId)
  FROM dbo.Questions
  WHERE SectionId = 6584
)
UPDATE trg
  SET QuestionId = src.QuestionId
  FROM trg INNER JOIN src
  ON trg.r = src.r;