Home » SQL & PL/SQL » SQL & PL/SQL » How to swap two rows ina table without creating any temporary storage?
How to swap two rows ina table without creating any temporary storage? [message #248390] Thu, 28 June 2007 18:34 Go to next message
Messages: 1
Registered: June 2007
Junior Member
Can any one suggest a method to swap two rows in a table using a single sql query???

like if I ve a table with columns name & id

& two entries "aa, 1"
"bb, 2"

i want to swap these two rows to enter it as "bb,2"

[Updated on: Thu, 28 June 2007 19:25]

Report message to a moderator

Re: How to swap two rows ina table without creating any temporary storage? [message #248403 is a reply to message #248390] Thu, 28 June 2007 22:20 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Are you saying that you want the rows to remain unchanged, but that they need to be physically relocated within the table?

If so, you're treading on very dangerous ground. Why do you want to do this? Do you want the rows to come out of a SQL in a particular order? Because this is not the way to do it! To order data you need an ORDER BY. End of story.

There is an argument for physically rearranging the rows of a table for clustering purposes. The idea is to place rows that are accessed together frequently in the same block. The correct solution to this problem is to create a HASH CLUSTER or an INDEX CLUSTER.

If this is just a purely theoretical exercise - and you don't want to have to explain your motivation to the likes of me, it can be done as follows:

UPDATE mytab a
SET (pk, col1, col2, col3) = (
    SELECT pk, col1, col2, col3
    FROM   mytab
    WHERE  pk = DECODE(a.pk, 'aa', 'bb', 'aa')
WHERE pk IN ('aa','bb')

It actually swaps the rows in-place without key violation or corruption - it's quite neat.

Be warned, if you do this on a large scale (I have) you will badly fragment the indexes and they will need to be rebuilt.

Let me re-iterate: you almost certainly do NOT want to do this. Whatever your problem is, there is a better solution.

Ross Leishman
Previous Topic: creating external table problem
Next Topic: getting the values from a cursor on different tables
Goto Forum:

Current Time: Tue Aug 22 23:26:11 CDT 2017

Total time taken to generate the page: 0.13152 seconds