在 MS SQLServer 中交换两行，保留原始主键且无需手动更新

发布于 2024-07-13 05:36:53 字数 278 浏览 5 评论 0原文

我有以下问题：我有一些行，

ID   CODE     NAME            .........
1    h1100h1  Cool example1   .........
2    h654441  Another cool1   .........

我想交换它们，保留所有旧的主键和约束。当然，我可以通过更新行轻松手动解决这个问题。我想知道是否有人对此类问题有任何出色的解决方案，而不仅仅是手动执行更新命令。我真的非常感谢任何建议或建议。

原文

I have the following problem : I have rows like

ID   CODE     NAME            .........
1    h1100h1  Cool example1   .........
2    h654441  Another cool1   .........

I would like to swap them retaining all old primary keys and constraints. Of course, I can easily solve this manually by updating the rows. I am kind of wondering whether anybody has any excellent solution for this kind of problem instead of just executing update command manually. I really really appreciate any suggestions or recommendations.

分享到QQ

分享到微博

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

发布评论

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

月依秋水 2024-07-20 05:36:53

我还没有测试过这个，但我认为它会起作用。我假设 id 是单列主键。如果情况并非如此，那么您将需要稍微调整此代码以处理 PK。

UPDATE
     T1
SET
     column_1 = T2.column_1,
     column_2 = T2.column_2,
     ...
FROM
     dbo.My_Table T1
INNER JOIN dbo.My_Table T2 ON
     T2.id =
          CASE
               WHEN T1.id = @id_1 THEN @id_2
               WHEN T1.id = @id_2 THEN @id_1
               ELSE NULL
          END
WHERE
     T1.id IN (@id_1, @id_2)

I haven't tested this, but I think it will work. I'm assuming that id is a single-column Primary Key. If that's not the case then you will need to adjust this code slightly to handle the PK.

UPDATE
     T1
SET
     column_1 = T2.column_1,
     column_2 = T2.column_2,
     ...
FROM
     dbo.My_Table T1
INNER JOIN dbo.My_Table T2 ON
     T2.id =
          CASE
               WHEN T1.id = @id_1 THEN @id_2
               WHEN T1.id = @id_2 THEN @id_1
               ELSE NULL
          END
WHERE
     T1.id IN (@id_1, @id_2)

回复收藏 0 原文