SQL statement to switch values

Question

Hi, I have several tables where a field is for priority (1 to 5). Problem here is that different projects have been using 5 as highest and some 1 for highest and I going to harmonize this.

My easy option is to create a temp table and copy the data over and switch as this table:
1 -> 5
2 -> 4
3 -> 3
4 -> 2
5 -> 1

I'm not that good with SQL but it feels that there should be an easy way to switch those values right off with an statement but I do have concerns of when there are huge amount of data and if something goes wrong half way then the data will be in a mess.

Should I just go with my temp table solution or should do you have a nice way of doing this straight in SQL? (Oracle 10g is being used)

Many thanks!

Answer 1

simply update the second table like this, a temp table is not needed because you are just reversing the priority:

update table_2
set priority = 6-priority;

Answer 2

You can use a CASE statement

case PRIORITY
  when 5 then 1
  when 4 then 2
  when 3 then 3
  when 2 then 4
  when 1 then 5
  else PRIORITY
end

Edit: texBlues' solution is much better, but I leave this here for cases where the maths isn't as neat.

Answer 3

To be sure that no 'mess' results if the update goes awry, use a transaction. Building on tekBlues solution (+1 for this).

START TRANSACTION;

update table_2
set priority = 6-priority;

...

COMMIT;

This is especially valid if you want to update multiple tables in one go. Single statements are implicitely handled, as hainstech pointed out in his comment correctly.