<p>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:</p>

<ol>
<li>Delete both rows and re-insert them.</li>
<li>Update rows with some other value
and swap and then update to actual value.</li>
</ol>

<p>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?</p>

[{'answer_id': 665, 'author': 'Daan', 'author_id': 197, 'author_profile': 'https://Stackoverflow.com/users/197', 'pm_score': 5, 'selected': True, 'text': "<p>I think you should go for solution 2. There is no 'swap' function in any SQL variant I know of.</p>\n\n<p>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.</p>\n\n<p>But in short: there is no other solution than the ones you provided.</p>\n"}, {'answer_id': 684, 'author': 'Yaakov Ellis', 'author_id': 51, 'author_profile': 'https://Stackoverflow.com/users/51', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>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.</p>\n'}, {'answer_id': 73258, 'author': 'Andy Irving', 'author_id': 8553, 'author_profile': 'https://Stackoverflow.com/users/8553', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>CREATE TABLE testing\n(\n    cola int NOT NULL,\n    colb CHAR(1) NOT NULL\n);\n\nCREATE UNIQUE INDEX UIX_testing_a ON testing(colb);\n\nINSERT INTO testing VALUES (1, 'b');\nINSERT INTO testing VALUES (2, 'a');\n\nSELECT * FROM testing;\n\nUPDATE testing\nSET colb = CASE cola WHEN 1 THEN 'a'\n                WHEN 2 THEN 'b'\n                END\nWHERE cola IN (1,2);\n\nSELECT * FROM testing;\n</code></pre>\n\n<p>so you will go from:</p>\n\n<pre><code>cola    colb\n------------\n1       b\n2       a\n</code></pre>\n\n<p>to:</p>\n\n<pre><code>cola    colb\n------------\n1       a\n2       b\n</code></pre>\n"}, {'answer_id': 355870, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': "<p>Further to Andy Irving's answer</p>\n\n<p>this worked for me (on SQL Server 2005) in a similar situation\nwhere I have a composite key and I need to swap a field which is part of the unique constraint.</p>\n\n<p>key:  pID, LNUM \nrec1: 10, 0\nrec2: 10, 1\nrec3: 10, 2</p>\n\n<p>and I need to swap LNUM so that the result is</p>\n\n<p>key:  pID, LNUM\nrec1: 10, 1\nrec2: 10, 2\nrec3: 10, 0</p>\n\n<p>the SQL needed:</p>\n\n<pre><code>UPDATE    DOCDATA    \nSET       LNUM = CASE LNUM\n              WHEN 0 THEN 1\n              WHEN 1 THEN 2 \n              WHEN 2 THEN 0 \n          END\nWHERE     (pID = 10) \n  AND     (LNUM IN (0, 1, 2))\n</code></pre>\n"}, {'answer_id': 1036322, 'author': 'the.jxc', 'author_id': 84949, 'author_profile': 'https://Stackoverflow.com/users/84949', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>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.</p>\n\n<p>I\'m hoping that PostgreSQL will allow me to do this shuffle in the before trigger.</p>\n\n<p>I\'ll post back and let you know my mileage.</p>\n'}, {'answer_id': 2480068, 'author': 'BlueRaja - Danny Pflughoeft', 'author_id': 238419, 'author_profile': 'https://Stackoverflow.com/users/238419', 'pm_score': 1, 'selected': False, 'text': '<p>Oracle has deferred integrity checking which solves exactly this, but it is not available in either SQL Server or MySQL.</p>\n'}, {'answer_id': 10018113, 'author': 'Mark Gaulin', 'author_id': 811161, 'author_profile': 'https://Stackoverflow.com/users/811161', 'pm_score': 3, 'selected': False, 'text': "<p>There is another approach that works with SQL Server: use a temp table join to it in your UPDATE statement.</p>\n\n<p>The problem is caused by having two rows with the same value <em>at the same time</em>, but if you update both rows at once (to their new, unique values), there is no constraint violation.</p>\n\n<p>Pseudo-code:</p>\n\n<pre><code>-- setup initial data values:\ninsert into data_table(id, name) values(1, 'A')\ninsert into data_table(id, name) values(2, 'B')\n\n-- create temp table that matches live table\nselect top 0 * into #tmp_data_table from data_table\n\n-- insert records to be swapped\ninsert into #tmp_data_table(id, name) values(1, 'B')\ninsert into #tmp_data_table(id, name) values(2, 'A')\n\n-- update both rows at once! No index violations!\nupdate data_table set name = #tmp_data_table.name\nfrom data_table join #tmp_data_table on (data_table.id = #tmp_data_table.id)\n</code></pre>\n\n<p>Thanks to Rich H for this technique.\n- Mark</p>\n"}, {'answer_id': 10252174, 'author': 'Ben Mosher', 'author_id': 344143, 'author_profile': 'https://Stackoverflow.com/users/344143', 'pm_score': 2, 'selected': False, 'text': "<p>In SQL Server, the MERGE statement can update rows that would normally break a UNIQUE KEY/INDEX. (Just tested this because I was curious.)</p>\n\n<p>However, you'd have to use a temp table/variable to supply MERGE w/ the necessary rows.</p>\n"}, {'answer_id': 12437451, 'author': 'wildplasser', 'author_id': 905902, 'author_profile': 'https://Stackoverflow.com/users/905902', 'pm_score': 5, 'selected': False, 'text': '<p>The magic word is <strong>DEFERRABLE</strong> here:</p>\n\n<pre><code>DROP TABLE ztable CASCADE;\nCREATE TABLE ztable\n    ( id integer NOT NULL PRIMARY KEY\n    , payload varchar\n    );\nINSERT INTO ztable(id,payload) VALUES (1,\'one\' ), (2,\'two\' ), (3,\'three\' );\nSELECT * FROM ztable;\n\n\n    -- This works, because there is no constraint\nUPDATE ztable t1\nSET payload=t2.payload\nFROM ztable t2\nWHERE t1.id IN (2,3)\nAND t2.id IN (2,3)\nAND t1.id &lt;&gt; t2.id\n    ;\nSELECT * FROM ztable;\n\nALTER TABLE ztable ADD CONSTRAINT OMG_WTF UNIQUE (payload)\n    DEFERRABLE INITIALLY DEFERRED\n    ;\n\n    -- This should also work, because the constraint \n    -- is deferred until "commit time"\nUPDATE ztable t1\nSET payload=t2.payload\nFROM ztable t2\nWHERE t1.id IN (2,3)\nAND t2.id IN (2,3)\nAND t1.id &lt;&gt; t2.id\n    ;\nSELECT * FROM ztable;\n</code></pre>\n\n<p>RESULT:</p>\n\n<pre><code>DROP TABLE\nNOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ztable_pkey" for table "ztable"\nCREATE TABLE\nINSERT 0 3\n id | payload\n----+---------\n  1 | one\n  2 | two\n  3 | three\n(3 rows)\n\nUPDATE 2\n id | payload\n----+---------\n  1 | one\n  2 | three\n  3 | two\n(3 rows)\n\nNOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "omg_wtf" for table "ztable"\nALTER TABLE\nUPDATE 2\n id | payload\n----+---------\n  1 | one\n  2 | two\n  3 | three\n(3 rows)\n</code></pre>\n'}, {'answer_id': 36892474, 'author': 'TheBakker', 'author_id': 3215673, 'author_profile': 'https://Stackoverflow.com/users/3215673', 'pm_score': 2, 'selected': False, 'text': '<p>For Oracle there is an option, DEFERRED, but you have to add it to your constraint.</p>\n\n<pre><code>SET CONSTRAINT emp_no_fk_par DEFERRED; \n</code></pre>\n\n<p>To defer ALL constraints that are deferrable during the entire session, you can use the ALTER SESSION SET constraints=DEFERRED statement. </p>\n\n<p><a href="http://www.databasedesign-resource.com/deferred-integrity-constraints.html" rel="nofollow">Source</a></p>\n'}, {'answer_id': 42877932, 'author': 'Lis', 'author_id': 5560928, 'author_profile': 'https://Stackoverflow.com/users/5560928', 'pm_score': 2, 'selected': False, 'text': "<p>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. </p>\n\n<p>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.</p>\n"}, {'answer_id': 53168583, 'author': 'Lazy Programmer', 'author_id': 9355939, 'author_profile': 'https://Stackoverflow.com/users/9355939', 'pm_score': 1, 'selected': False, 'text': '<p>1)  switch the ids for name </p>\n\n<pre><code>id    student \n\n1     Abbot   \n2     Doris  \n3     Emerson \n4     Green  \n5     Jeames  \n</code></pre>\n\n<p>For the sample input, the output is:</p>\n\n<p>id    student </p>\n\n<pre><code>1     Doris   \n2     Abbot   \n3     Green   \n4     Emerson \n5     Jeames  \n</code></pre>\n\n<p>"in case n number of rows how will manage......"</p>\n'}]
['https://Stackoverflow.com/questions/644', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/191/']