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 performance?
You can use a DECODE statement:
update t1 set col2=decode(col2,a,1,b,2....)
or a CASE statement:
update t1 set col2=CASE when col2= a then 1 when col2=b then 2...end;
As for performance, I can't estimate that; it all depends on your hardware, database layout, how many indexes are on the table, et cetera.