Swap unique indexed column values in database *

Question

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:

1. Delete both rows and re-insert them
2. 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?

Answer

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.

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/644/" >Swap unique indexed column values in database< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.