sql交换主键值 [英] sql swap primary key values

问题描述

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

is it possible to swap primary key values between two datasets? If so, how would one do that?

推荐答案

为了简单起见，假设你有两条记录

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

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

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

都来自表 t(但它们可以来自不同的表)

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

你可以这样做

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.

警告:t.id = t2.id, t2.id = t.id 之所以起作用，是因为在 SQL 中更新发生在事务级别.t.id 不是变量，= 不是赋值.您可以将其解释为将 t.id 设置为查询效果之前 t2.id 的值，将 t2.id 设置为查询效果之前 t.id 的值".但是，某些数据库可能无法进行适当的隔离，例如，请参阅此问题(但是，运行上面的查询，这可能被认为是多表更新，按照 mysql 中的标准运行).

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).

这篇关于sql交换主键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！