CODEWITHSUNDEEP
oracle-databasesql-update
Solid1Snake1
Solid1Snake1

Reputation: 272

Oracle numbering update

I am trying to do some SQL working with Oracle

I have a table that contains text data, and an order list of numbers of how that text should appear, 1-27.

So:
Bonjour | 1
mon nom | 2
Jean P. | 3
Hello J | 4
Je suis | 5
is John | 6

Now I have to reorder the list numbers: 5 to number 2, and move 2 to 3, 3 to 4, 4 to 5, BUT NOT 5 to 6 or 6 to anything. Remember, this is a list to number 27.

So I'll have:
Bonjour | 1
mon nom | 2
Jean P. | 3
Hello J | 4
My name | 5
is John | 6

Does anyone know of a good way to go about doing this?

Upvotes: 0

Views: 59

Answers (1)

user330315
user330315

Reputation:

Something like:

update foobar
   set sort_nr = case 
                   when sort_nr = 5 then 2
                   when sort_nr = 2 then 3
                   when sort_nr = 3 then 4
                   when sort_nr = 4 then 5
                   else sort_nr
                end
where sort_nr in (2,3,4,5);

The else part in the case is not stricly necessary. But in case you forget the where clause it prevent accidental wrong updates.

Here is an SQLFiddle example: http://sqlfiddle.com/#!4/52d50/1

Upvotes: 2

Related Questions