sql - two - update query swap values
Swap unique indexed column values in database (8)
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?
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:
1 Doris 2 Abbot 3 Green 4 Emerson 5 Jeames
"in case n number of rows how will manage......"
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
cola colb ------------ 1 a 2 b
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.
key: pID, LNUM rec1: 10, 0 rec2: 10, 1 rec3: 10, 2
and I need to swap LNUM so that the result is
key: pID, LNUM rec1: 10, 1 rec2: 10, 2 rec3: 10, 0
the SQL needed:
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))
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.
I think you should go for solution 2. There is no 'swap' function in any SQL variant I know of.
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.
But in short: there is no other solution than the ones you provided.
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.
The magic word is DEFERRABLE here:
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)