CODEWITHSUNDEEP
oracle-databaseoracle10g
King
King

Reputation: 31

single Column Update for multiple rows in oracle10g

Update table_1 set col1= 1 where col2 = 'ONE';  
update table_1 set col1= 2 where col2 = 'TWO';  
Update table_1 set col1= 3 where col2 = 'THREE';
...
update table_1 set col1= 100 where col2 = 'HUNDRED';

Is there any simplified way to achive this in a single query instead of writing 100 update statemnets in oracle10g??

Upvotes: 0

Views: 1537

Answers (2)

Lord Peter
Lord Peter

Reputation: 3501

You could make use of the Julian spelling formats (search asktom.oracle.com for more details)

Here's output from my session:-

create table table_1 (col_1 number, col_2 varchar(20))

insert into table_1 (col_1, col_2) values (null, 'THIRTY-THREE')
insert into table_1 (col_1, col_2) values (null, 'SEVEN')
insert into table_1 (col_1, col_2) values (null, 'EIGHTY-FOUR')

select * from table_1 
COL_1     COL_2     
       THIRTY-THREE     
       SEVEN    
       EIGHTY-FOUR

update /*+bypass_ujvc*/
(select t1.col_1, spelled.n
from
table_1 t1
inner join
(select n, to_char(to_date (n, 'J'),'JSP') spelled_n from
(select level n from dual connect by level <= 100)) spelled
on t1.col_2 = spelled.spelled_n
)
set col_1 = n

select * from table_1

COL_1 COL_2
33     THIRTY-THREE
7      SEVEN
84     EIGHTY-FOUR

The nasty hint (bypass_ujvc) ignores the fact that the inline view isn't key preserved - in practice you should use a merge statement instead. But this isn't a real-world scenario, right! (And you'll have to treat your 'HUNDRED' as a special case = 'ONE HUNDRED'.)

Upvotes: 0

miherrma
miherrma

Reputation: 336

I think there might be a solution with Oracle Case-Statement or the decode-function, although it will be a quite long statement and I am not quite sure what the advantage over 100 update statements might be. Also I am not aware of any limitations regarding length of parameter-lists, etc.

Example for Case:

update table_1
set col1 = CASE col2
  WHEN 'ONE' THEN 1
  WHEN 'TWO' THEN 2
  WHEN 'THREE' THEN 3
  WHEN 'FOUR' THEN 4
  WHEN 'FIVE' THEN 5
  WHEN 'SIX' THEN 6
  WHEN 'SEVEN' THEN 7
  WHEN 'EIGHT' THEN 8
  ...
  WHEN 'HUNDRED' THEN 100
  ELSE col2
END;

Example for decode:

update table_1
set col1 = decode(col2,
              'ONE', 1,
              'TWO', 2,
              'THREE', 3,
              'FOUR', 4,
              'FIVE', 5,
              'SIX', 6,
              'SEVEN', 7,
              'EIGHT', 8,
              ...
              'HUNDRED', 100,
              col2);

Upvotes: 2

Related Questions