sql -交换数据库中唯一的索引列值

Translate

我有一个数据库表,并且其中一个字段(不是主键)上有一个唯一索引。现在,我想将该列下的值交换为两行。怎么办呢?我知道的两个技巧是:

  1. 删除两行,然后重新插入。
  2. 用其他值更新行并交换,然后更新为实际值。

但是我不想追求这些,因为它们似乎并不是解决问题的适当方法。有人可以帮我吗?

This question and all comments follow the "Attribution Required."

所有的回答

Translate

我认为您应该寻求解决方案2。据我所知,在任何SQL变体中都没有“交换”功能。

如果您需要定期执行此操作,则建议解决方案1,具体取决于软件的其他部分如何使用此数据。如果不小心,可能会遇到锁定问题。

简而言之:除了您提供的解决方案外,没有其他解决方案。

来源
Translate

魔术词是优惠这里:

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;

结果:

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)
来源
Corey Lee
Translate

进一步回答安迪·欧文(Andy Irving)

这对我(在SQL Server 2005上)很有效,在类似的情况下,我有一个复合键,需要交换作为唯一约束一部分的字段。

密钥:pID,LNUM rec1:10、0 rec2:10、1 rec3:10、2

我需要交换LNUM,以便结果是

密钥:pID,LNUM rec1:10,1 rec2:10,2 rec3:10,0

需要的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))
来源
Translate

SQL Server还有另一种方法:在UPDATE语句中使用临时表联接。

该问题是由具有相同值的两行引起的与此同时,但是如果您一次更新两行(为其新的唯一值),则不会违反约束。

伪代码:

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

感谢Rich H的这项技术。 -马克

来源
Translate

我还认为#2是最好的选择,尽管我会确保将其包装在事务中,以防更新期间出现问题。

用不同的值更新唯一索引值的另一种方法(是您要求的)是将行中的所有其他值更新为另一行的值。这样做意味着您可以单独保留唯一索引值,最后,最终得到所需的数据。但是,要小心,以防其他表在外键关系中引用该表,则数据库中的所有关系均保持不变。

来源
Translate

假设您知道要更新的两行的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;

因此您将来自:

cola    colb
------------
1       b
2       a

至:

cola    colb
------------
1       a
2       b
来源
Translate

我也有同样的问题。这是我在PostgreSQL中建议的方法。就我而言,我的唯一索引是一个序列值,在我的行上定义了一个明确的用户顺序。用户将在网络应用程序中随机排列行,然后提交更改。

我打算添加一个“之前”触发器。在该触发器中,每当我更新唯一索引值时,我都会查看是否有其他行已经保存了我的新值。如果是这样,我会给他们我以前的价值,并有效地窃取他们的价值。

我希望PostgreSQL将允许我在before触发器中进行此混洗。

我会回发,让您知道我的里程。

来源
Translate

对于Oracle,有一个选项DEFERRED,但是您必须将其添加到约束中。

SET CONSTRAINT emp_no_fk_par DEFERRED; 

要推迟在整个会话期间可推迟的所有约束,可以使用ALTER SESSION SET约束= DEFERRED语句。

资源

来源
Lis
Translate

我通常会想到一个绝对没有表中索引的值。通常-对于唯一的列值-这真的很容易。例如,对于“位置”列的值(有关多个元素的顺序的信息),该值为0。

然后,您可以将值A复制到变量,用值B更新它,然后从变量中设置值B。有两个查询,不过我没有更好的解决方案。

来源
Translate

Oracle推迟了完整性检查,可以完全解决此问题,但是在SQL Server或MySQL中均不可用。

来源
Translate

在SQL Server中,MERGE语句可以更新通常会破坏UNIQUE KEY / INDEX的行。 (只是测试了一下,因为我很好奇。)

但是,您必须使用临时表/变量来提供带有必要行的MERGE。

来源
Translate

1)切换名称的ID

id    student 

1     Abbot   
2     Doris  
3     Emerson 
4     Green  
5     Jeames  

对于样本输入,输出为:

身份学生

1     Doris   
2     Abbot   
3     Green   
4     Emerson 
5     Jeames  

“在n行的情况下将如何进行管理……”

来源
下一个问题:
c# -自动更新版本号