SQL 交换数据库中的唯一索引列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Swap unique indexed column values in database
提问by Ramesh Soni
I have a database table and one of the fields (not the primary key) is having a unique index on it. Now I want to swap values under this column for two rows. How could this be done? Two hacks I know are:
我有一个数据库表，其中一个字段（不是主键）有一个唯一索引。现在我想将此列下的值交换为两行。这怎么可能？我知道的两个黑客是：
- Delete both rows and re-insert them.
- Update rows with some other value and swap and then update to actual value.
- 删除两行并重新插入它们。
- 使用其他值更新行并交换，然后更新为实际值。
But I don't want to go for these as they do not seem to be the appropriate solution to the problem. Could anyone help me out?
但我不想做这些，因为它们似乎不是解决问题的合适方法。有人可以帮我吗？
采纳答案by Daan
I think you should go for solution 2. There is no 'swap' function in any SQL variant I know of.
我认为您应该选择解决方案 2。我所知道的任何 SQL 变体中都没有“交换”功能。
If you need to do this regularly, I suggest solution 1, depending on how other parts of the software are using this data. You can have locking issues if you're not careful.
如果您需要定期执行此操作，我建议使用解决方案 1，具体取决于软件的其他部分如何使用这些数据。如果您不小心，您可能会遇到锁定问题。
But in short: there is no other solution than the ones you provided.
但简而言之：除了您提供的解决方案之外，没有其他解决方案。
回答by wildplasser
The magic word is DEFERRABLEhere:
神奇的词在这里是可延迟的：
DROP TABLE ztable CASCADE;
CREATE TABLE ztable
( id integer NOT NULL PRIMARY KEY
, payload varchar
);
INSERT INTO ztable(id,payload) VALUES (1,'one' ), (2,'two' ), (3,'three' );
SELECT * FROM ztable;
-- This works, because there is no constraint
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
;
SELECT * FROM ztable;
ALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)
DEFERRABLE INITIALLY DEFERRED
;
-- This should also work, because the constraint
-- is deferred until "commit time"
UPDATE ztable t1
SET payload=t2.payload
FROM ztable t2
WHERE t1.id IN (2,3)
AND t2.id IN (2,3)
AND t1.id <> t2.id
;
SELECT * FROM ztable;
RESULT:
结果：
DROP TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable"
CREATE TABLE
INSERT 0 3
id | payload
----+---------
1 | one
2 | two
3 | three
(3 rows)
UPDATE 2
id | payload
----+---------
1 | one
2 | three
3 | two
(3 rows)
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable"
ALTER TABLE
UPDATE 2
id | payload
----+---------
1 | one
2 | two
3 | three
(3 rows)
回答by Daan
Further to Andy Irving's answer
进一步安迪欧文的回答
this worked for me (on SQL Server 2005) in a similar situation where I have a composite key and I need to swap a field which is part of the unique constraint.
这对我（在 SQL Server 2005 上）有用，在类似的情况下，我有一个复合键，我需要交换一个字段，该字段是唯一约束的一部分。
key: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2
键：pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2
and I need to swap LNUM so that the result is
我需要交换 LNUM 以便结果是
key: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0
键：pID，LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0
the SQL needed:
所需的 SQL：
UPDATE DOCDATA
SET LNUM = CASE LNUM
WHEN 0 THEN 1
WHEN 1 THEN 2
WHEN 2 THEN 0
END
WHERE (pID = 10)
AND (LNUM IN (0, 1, 2))
回答by Mark Gaulin
There is another approach that works with SQL Server: use a temp table join to it in your UPDATE statement.
还有另一种适用于 SQL Server 的方法：在 UPDATE 语句中使用临时表联接。
The problem is caused by having two rows with the same value at the same time, but if you update both rows at once (to their new, unique values), there is no constraint violation.
该问题是由具有相同值的两行导致在同一时间，但如果你在一次（到其新的，独特的价值观）更新两行，没有违反约束。
Pseudo-code:
伪代码：
-- setup initial data values:
insert into data_table(id, name) values(1, 'A')
insert into data_table(id, name) values(2, 'B')
-- create temp table that matches live table
select top 0 * into #tmp_data_table from data_table
-- insert records to be swapped
insert into #tmp_data_table(id, name) values(1, 'B')
insert into #tmp_data_table(id, name) values(2, 'A')
-- update both rows at once! No index violations!
update data_table set name = #tmp_data_table.name
from data_table join #tmp_data_table on (data_table.id = #tmp_data_table.id)
Thanks to Rich H for this technique. - Mark
感谢 Rich H 提供的这项技术。- 标记
回答by Yaakov Ellis
I also think that #2 is the best bet, though I would be sure to wrap it in a transaction in case something goes wrong mid-update.
我也认为 #2 是最好的选择，尽管我一定会把它包装在一个交易中，以防在更新过程中出现问题。
An alternative (since you asked) to updating the Unique Index values with different values would be to update all of the other values in the rows to that of the other row. Doing this means that you could leave the Unique Index values alone, and in the end, you end up with the data that you want. Be careful though, in case some other table references this table in a Foreign Key relationship, that all of the relationships in the DB remain intact.
使用不同值更新唯一索引值的替代方法（根据您的要求）是将行中的所有其他值更新为另一行的值。这样做意味着您可以单独保留唯一索引值，最后，您会得到您想要的数据。但是要小心，以防其他表在外键关系中引用此表，数据库中的所有关系都保持不变。
回答by Andy Irving
Assuming you know the PK of the two rows you want to update... This works in SQL Server, can't speak for other products. SQL is (supposed to be) atomic at the statement level:
假设你知道你要更新的两行的PK......这在SQL Server中有效，不能代表其他产品。SQL 在语句级别是（应该是）原子的：
CREATE TABLE testing
(
cola int NOT NULL,
colb CHAR(1) NOT NULL
);
CREATE UNIQUE INDEX UIX_testing_a ON testing(colb);
INSERT INTO testing VALUES (1, 'b');
INSERT INTO testing VALUES (2, 'a');
SELECT * FROM testing;
UPDATE testing
SET colb = CASE cola WHEN 1 THEN 'a'
WHEN 2 THEN 'b'
END
WHERE cola IN (1,2);
SELECT * FROM testing;
so you will go from:
所以你将从：
cola colb
------------
1 b
2 a
to:
到：
cola colb
------------
1 a
2 b
回答by the.jxc
I have the same problem. Here's my proposed approach in PostgreSQL. In my case, my unique index is a sequence value, defining an explicit user-order on my rows. The user will shuffle rows around in a web-app, then submit the changes.
我也有同样的问题。这是我在 PostgreSQL 中提出的方法。在我的例子中，我的唯一索引是一个序列值，在我的行上定义一个显式的用户顺序。用户将在网络应用程序中随机排列行，然后提交更改。
I'm planning to add a "before" trigger. In that trigger, whenever my unique index value is updated, I will look to see if any other row already holds my new value. If so, I will give them my old value, and effectively steal the value off them.
我打算添加一个“之前”触发器。在该触发器中，每当我的唯一索引值更新时，我都会查看是否有任何其他行已经包含我的新值。如果是这样，我会给他们我的旧价值，并有效地窃取他们的价值。
I'm hoping that PostgreSQL will allow me to do this shuffle in the before trigger.
我希望 PostgreSQL 允许我在 before 触发器中进行这种洗牌。
I'll post back and let you know my mileage.
我会回帖，让你知道我的里程。
回答by TheBakker
For Oracle there is an option, DEFERRED, but you have to add it to your constraint.
对于 Oracle，有一个选项 DEFERRED，但您必须将其添加到您的约束中。
SET CONSTRAINT emp_no_fk_par DEFERRED;
To defer ALL constraints that are deferrable during the entire session, you can use the ALTER SESSION SET constraints=DEFERRED statement.
要推迟在整个会话期间可推迟的所有约束，您可以使用 ALTER SESSION SET 约束 = DEFERRED 语句。
回答by Lis
I usually think of a value that absolutely no index in my table could have. Usually - for unique column values - it's really easy. For example, for values of column 'position' (information about the order of several elements) it's 0.
我通常会想到一个值，我的表中绝对没有索引。通常 - 对于唯一的列值 - 这真的很容易。例如，对于列 'position' 的值（关于几个元素的顺序的信息），它是 0。
Then you can copy value A to a variable, update it with value B and then set value B from your variable. Two queries, I know no better solution though.
然后你可以将值 A 复制到一个变量，用值 B 更新它，然后从你的变量中设置值 B。两个查询，但我不知道更好的解决方案。
回答by Lazy Programmer
1) switch the ids for name
1）切换名称的id
id student
1 Abbot
2 Doris
3 Emerson
4 Green
5 Jeames
For the sample input, the output is:
对于样本输入，输出为：
id student
身学生
1 Doris
2 Abbot
3 Green
4 Emerson
5 Jeames
"in case n number of rows how will manage......"
“万一有n行怎么办……”
