데이터베이스에서 고유 한 인덱싱 된 열 값 교체

데이터베이스에서 고유 한 인덱싱 된 열 값 교체

---

데이터베이스 테이블이 있고 필드 중 하나 (기본 키가 아님)에 고유 인덱스가 있습니다. 이제이 열 아래의 값을 두 행으로 바꾸고 싶습니다. 어떻게 할 수 있습니까? 내가 아는 두 가지 해킹은 다음과 같습니다.

1. 두 행을 모두 삭제하고 다시 삽입하십시오.
2. 다른 값으로 행을 업데이트하고 교체 한 다음 실제 값으로 업데이트합니다.

그러나 나는 이것들이 문제에 대한 적절한 해결책이 아닌 것 같아서 가고 싶지 않습니다. 누구든지 나를 도울 수 있습니까?

---

솔루션 2로 가야한다고 생각합니다. 내가 아는 SQL 변형에는 '스왑'기능이 없습니다.

이 작업을 정기적으로 수행해야하는 경우 소프트웨어의 다른 부분에서이 데이터를 사용하는 방식에 따라 솔루션 1을 제안합니다. 주의하지 않으면 잠금 문제가 발생할 수 있습니다.

그러나 간단히 말해서, 당신이 제공 한 것 외에 다른 해결책은 없습니다.

---

마법의 단어는 여기에서 DEFERRABLE입니다 .

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)

---

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

---

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에게 감사합니다. -마크

---

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.

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.

---

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:

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

---

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.

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.

I'll post back and let you know my mileage.

---

For Oracle there is an option, DEFERRED, but you have to add it to your constraint.

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.

Source

---

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.

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.

---

Oracle has deferred integrity checking which solves exactly this, but it is not available in either SQL Server or MySQL.

---

In SQL Server, the MERGE statement can update rows that would normally break a UNIQUE KEY/INDEX. (Just tested this because I was curious.)

However, you'd have to use a temp table/variable to supply MERGE w/ the necessary rows.

---

1) switch the ids for name

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

ReferenceURL : https://stackoverflow.com/questions/644/swap-unique-indexed-column-values-in-database