当前位置：文江博客话题详情

sql交换主键值

发布于 2024-09-01 19:21:45 字数 39 浏览 7 评论 0原文

是否可以在两个数据集之间交换主键值？如果是这样，人们会怎样做呢？

原文

分享到QQ

分享到微博

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

发布评论

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

幻想少年梦 2024-09-08 19:21:45

为了简单起见，假设您有

id   name
---------
1    john

id   name
---------
2    jim

来自表 t 的两条记录（但它们可以来自不同的表）

您可以这样做

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id
WHERE t.id = 1 AND t2.id = 2

注意：
更新主键还有其他副作用，也许首选方法是保留主键不变并交换所有其他列的值。

注意事项：
t.id = t2.id, t2.id = t.id 之所以有效，是因为在 SQL 中更新发生在事务级别。 t.id 不是变量，= 不是赋值。您可以将其解释为“将 t.id 设置为查询效果之前 t2.id 的值，将 t2.id 设置为查询效果之前 t.id 的值”。但是，某些数据库可能无法进行适当的隔离，例如，请参阅此问题（但是，运行上面的查询，这可能被认为是多表更新，其行为符合 mysql 中的标准）。

Let's for the sake of simplicity assume you have two records

id   name
---------
1    john

id   name
---------
2    jim

both from table t (but they can come from different tables)

You could do

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id
WHERE t.id = 1 AND t2.id = 2

Note:
Updating primary keys has other side effects and maybe the preferred approach would be to leave the primary keys as they are and swap the values of all the other columns.

Caveat:
The reason why the t.id = t2.id, t2.id = t.id works is because in SQL the update happens on a transaction level. The t.id is not variable and = is not assignment. You could interpret it as "set t.id to the value t2.id had before the effect of the query, set t2.id to the value t.id had before the effect of the query". However, some databases might not do proper isolation, see this question for example (however, running above query, which is probably considered multi table update, behaved according to the standard in mysql).

回复收藏 0 原文

回忆那么伤 2024-09-08 19:21:45

我更喜欢以下方法（Justin Cave 在某处写过类似的文章）：

update MY_TABLE t1
set t1.MY_KEY = (case when t1.MY_KEY = 100 then 101 else 100 end)
where t1.MYKEY in (100, 101)

I prefer the following approach (Justin Cave wrote similar somewhere):

update MY_TABLE t1
set t1.MY_KEY = (case when t1.MY_KEY = 100 then 101 else 100 end)
where t1.MYKEY in (100, 101)

回复收藏 0 原文

人生戏 2024-09-08 19:21:45

与@Bart的解决方案类似，但我使用了稍微不同的方式：

update t
set t.id=(select decode(t.id, 100, 101, 101, 100) from dual)
where t.id in (100, 101);

这是完全相同的，但我知道decode比case更好。

另外，为了让 @Bart 的解决方案对我有用，我必须添加一个 when：

update t
set t.id = (case when t.id = 100 then 101 else 101 end)
where t.id in (100, 101);

Similar to @Bart's solution, but I used a slightly different way:

update t
set t.id=(select decode(t.id, 100, 101, 101, 100) from dual)
where t.id in (100, 101);

This is quite the same, but I know decode better then case.

Also, to make @Bart's solution work for me I had to add a when:

update t
set t.id = (case when t.id = 100 then 101 else 101 end)
where t.id in (100, 101);

回复收藏 0 原文

月寒剑心 2024-09-08 19:21:45

如果您有 FOREIGN_KEYS 并且想要保留 AUTO_INCRMENT

BEGIN;
SET FOREIGN_KEY_CHECKS=0;
SET @from = 2;
SET @to = 3;
SET @tmpid = (2000000 + @from % 147483647);
SET @ai = (SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name');
UPDATE table_name SET id=@tmpid WHERE id = @from;
UPDATE table_name SET id=@from WHERE id=@to;
UPDATE table_name SET id=@to WHERE id = @tmpid;
SET FOREIGN_KEY_CHECKS=1;
SET @sql = CONCAT('ALTER TABLE `table_name` AUTO_INCREMENT = ', @ai);
PREPARE st FROM @sql;
EXECUTE st;
COMMIT;

If you have FOREIGN_KEYS and want to preserve AUTO_INCREMENT

BEGIN;
SET FOREIGN_KEY_CHECKS=0;
SET @from = 2;
SET @to = 3;
SET @tmpid = (2000000 + @from % 147483647);
SET @ai = (SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name');
UPDATE table_name SET id=@tmpid WHERE id = @from;
UPDATE table_name SET id=@from WHERE id=@to;
UPDATE table_name SET id=@to WHERE id = @tmpid;
SET FOREIGN_KEY_CHECKS=1;
SET @sql = CONCAT('ALTER TABLE `table_name` AUTO_INCREMENT = ', @ai);
PREPARE st FROM @sql;
EXECUTE st;
COMMIT;

回复收藏 0 原文

~没有更多了~