Updating Partitioned Tables

Recently there was a requirement to update a huge partition table. The change was to set a unique value to a column which is spread across year and month partition. I could achieve it easier with the help of partition clause in the update statement.

Below is the sample script used to update the table partition wise.

begin

for samplep in (select partition_name from user_tab_partitions where table_name='SAMPLETABLE')

loop

update sampletable s partition (samplep.partition_name) set s.samplename='x';

commit;

end loop;

end;

Table Structure looks similar to the below;

create table sampletable (id number,samplename varchar2(20),sampleyyyymm number)

partition by range (sampleyyyymm)

(

partition p1 values less than (200901),

partition p2 values less than (200902),

partition p3 values less than (200903),

partition p4 values less than (200904),

partition p5 values less than (200905),

partition p6 values less than (200906),

partition p7 values less than (200907),

partition p8 values less than (200908),

partition p9 values less than (200909),

partition p10 values less than (200910),

partition p11 values less than (200911),

partition p12 values less than (200912),

partition p13 values less than (maxvalue)

);