交换交换的过程，值主键，Oracle，ORA-00001

问题描述

TABLE_PRD

CREATE TABLE TABLE_PRD
(
  PRODUCT_CODE_PRD VARCHAR2(10) NOT NULL
, DESCRIPTION_PRODUCT_PRD VARCHAR2(20)
, CONSTRAINT TABLE_PRD_PK PRIMARY KEY
  (
    PRODUCT_CODE_PRD
  )
  ENABLE
);
INSERT INTO TABLE_PRD (PRODUCT_CODE_PRD, DESCRIPTION_PRODUCT_PRD) VALUES ('LS', 'Leasing');
INSERT INTO TABLE_PRD (PRODUCT_CODE_PRD, DESCRIPTION_PRODUCT_PRD) VALUES ('TG', 'Total Cost');

TABLE_POS

CREATE TABLE TABLE_POS
(
  POSITION_CODE_POS INTEGER NOT NULL
, SOME_TEXT_POS VARCHAR2(20)
, CONSTRAINT TABLE_POS_PK PRIMARY KEY
  (
    POSITION_CODE_POS
  )
  ENABLE
);
INSERT INTO TABLE_POS (POSITION_CODE_POS, COMMENT_POS) VALUES ('1', 'Owner');
INSERT INTO TABLE_POS (POSITION_CODE_POS, COMMENT_POS) VALUES ('2', 'Manager');
INSERT INTO TABLE_POS (POSITION_CODE_POS, COMMENT_POS) VALUES ('3', 'User');

TABLE_REL

CREATE TABLE TABLE_REL
(
  PRODUCT_CODE_REL VARCHAR2(10) NOT NULL
, POSITION_CODE_REL INTEGER NOT NULL
, DESCRIPTION_REL VARCHAR2(20)
, CREATION_DATE_REL DATE
, CONTENT BLOB
, CONSTRAINT TABLE_REL_FK1 FOREIGN KEY (PRODUCT_CODE_REL) REFERENCES TABLE_PRD (PRODUCT_CODE_PRD) ENABLE
, CONSTRAINT TABLE_REL_FK2 FOREIGN KEY (POSITION_CODE_REL) REFERENCES TABLE_POS (POSITION_CODE_POS) ENABLE
, CONSTRAINT TABLE_REL_PK PRIMARY KEY (PRODUCT_CODE_REL, POSITION_CODE_REL) ENABLE
);
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('LS', '1', 'Leasing - Owner');
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('LS', '2', 'Leasing - Manager');
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('LS', '3', 'Leasing - User');
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('TG', '1', 'Total Cost - Owner');
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('TG', '2', 'Total Cost - Manager');
INSERT INTO TABLE_REL (PRODUCT_CODE_REL, POSITION_CODE_REL, DESCRIPTION_REL) VALUES ('TG', '3', 'Total Cost - User');

现在，我需要执行圆形/圆形SWAP，更改POSITION_CODE_REL

Now, I need to perform a Circular/Round SWAP, changing the POSITION_CODE_REL

1 ->3(在 1 之前，是 3)，2 ->1(2 之前为 1)，3 ->2(3 之前是 2).

1 -> 3 (before of 1, is 3), 2 -> 1 (before of 2 is 1), 3 -> 2 (before of 3 is 2).

6 errors saving changes to table TABLE_REL:
Row 1: ORA-00001: unique constraint (TABLE_REL_PK) violated
Row 2: ORA-00001: unique constraint (TABLE_REL_PK) violated
Row 3: ORA-00001: unique constraint (TABLE_REL_PK) violated
Row 4: ORA-00001: unique constraint (TABLE_REL_PK) violated
Row 5: ORA-00001: unique constraint (TABLE_REL_PK) violated
Row 6: ORA-00001: unique constraint (TABLE_REL_PK) violated

我知道是什么情况，问题是，是否有一些程序可以执行SWAP?

I know what is the situation, The question is, Is There Some Procedure to perform the SWAP?

推荐答案

基于这些问题SQL - 如何选择具有最大值列的行

Oracle SQL:用另一个表中的数据更新一个表

和答案

https://stackoverflow.com/a/42007108/811293

检查此代码:

UPDATE TABLE_REL SET POSITION_CODE_REL =
    CASE
    WHEN POSITION_CODE_REL = 1 THEN /*MAX VALUE*/ (
        SELECT POSITION_CODE_REL FROM (SELECT * FROM TABLE_REL ORDER BY POSITION_CODE_REL DESC) WHERE rownum = 1
        )
    ELSE POSITION_CODE_REL - 1 END;

反向

UPDATE TABLE_REL SET POSITION_CODE_REL =
    CASE
    WHEN POSITION_CODE_REL = /*MAX VALUE*/ (
        SELECT POSITION_CODE_REL FROM (SELECT * FROM TABLE_REL ORDER BY POSITION_CODE_REL DESC) WHERE rownum = 1
        ) THEN 1
    ELSE POSITION_CODE_REL + 1 END;

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