Problem solve Get help with specific problems with your technologies, process and projects.

Best way of updating 400 column values in table

I have a table t1 with 5 million records. I have to update col2 of the table t1 as follows:

 update t1 set col2=1 where col2=a; update t1 set col2=2 where col2=b; update t1 set col2=3 where col2=c; update t1 set col2=4 where col2=d; update t1 set col2=5 where col2=e;

and so on for 400 more values.

Please let me know if there is a better way of updating the col2 of t1 instead of firing so many update statements. Can I do it with a single update? If so, then how, and how will it affect the performance? Please note that 1,2,3 and a,b,c, can be any var char values.

It would also be great if you can send the approximate performance numbers expected. Would it be 1 minute, 10 minutes, or 1 hour for the above scenario? At present, there is no index. Would indexing help in the update?

You do want to index the table on col2. Otherwise it will do full table scan for each updates.

For 400 plus different values, you may want to group them in different DECODE statements like the one below as you cannot use a single decode statement (I believe the maximum number of arguments is 255).

 UPDATE T1 set col2 = DECODE(col2, 'a', 1, 'b', 2, 'c',3, 'd', 4, 'e', 5, col2);

Secondly, performance depends on the range of values and the number of records for each value, so I cannot specifically answer your question about how long it will take.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.