I have a (very simplified) (Oracle) table like this. The row number are only for better understanding, they have no deeper meaning.

Now I find the "error" - the names are messed up. All "Jims" should be renamed to "John", John changes to Mike and Mike changes to Jim.

My first update changes Jim to John. My second update should change John to Mike.

But the real problem is: Now I have two Johns - the old and the renamed Jim. How to find and modify the "old John" without changing the other?

My idea is to use temp-values:

• Jim to temp_John
• John to temp_mike
• Mike to Jim
• temp_John to John
• temp_mike to Mike

Good idea? Or are there better ones? Or is it the totally wrong way?

Edit: I reality, there are not three names, but 700 - 1000 numbers, so 25 to 345 558 to 2 1 to 38 ...

CodePudding user response：

Nah, it's just a single UPDATE with a CASE expression:

SQL> select * From test order by crow;

      CROW VALU
---------- ----
         1 Jim
         2 John
         3 Mike

SQL> update test set
  2    value = case when value = 'Jim'  then 'John'
  3                 when value = 'John' then 'Mike'
  4                 when value = 'Mike' then 'Jim'
  5            end;

3 rows updated.

SQL> select * From test order by crow;

      CROW VALU
---------- ----
         1 John
         2 Mike
         3 Jim

SQL>

CodePudding user response：

You can use decode:

update test set value = decode(value,'JIM','JOHN','JOHN','MIKE','MIKE','JIM');

db<>fiddle here

For numbers you can use negative numbers as a temporarity:

update test value = decode(value, 65537, -7777776777777, value);
update test value = decode(value, 7777776777777, -45, value);
update test value = decode(value, 45, -65537, value);
update test set value = abs(value);

This way you can have as many numbers as you want as it uses one query per number.

updated db<>fiddle here for numbers example