Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table TEST
(
LOC_SID NUMBER(38,0),
CITY VARCHAR2(180 BYTE),
POSTAL_CD VARCHAR2(15 BYTE),
EFFECTIVE_DT_FROM DATE,
EFFECTIVE_DT_TO DATE,
VERSION NUMBER(38,0)
);

begin
Insert into TEST values (25101,'Assam',1153,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('01.01.17 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25102,'Assam',1153,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('31.12.99 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25103,'Assam',1290,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('01.01.17 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25104,'Assam',1290,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('31.12.99 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25105,'Assam',1310,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('01.01.17 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25106,'Assam',1310,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('31.12.99 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25107,'Assam',1781,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('01.01.17 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25108,'Assam',1781,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('31.12.99 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25109,'Assam',1982,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('01.01.17 23:59:59','DD.MM.YY HH24:MI:SS'),1);
Insert into TEST values (25110,'Assam',1982,to_date('01.01.00 00:00:00','DD.MM.YY HH24:MI:SS'),to_date('31.12.99 23:59:59','DD.MM.YY HH24:MI:SS'),1);
end;
/
1 rows affected

merge into test t
using (
    select
        loc_sid,
        row_number() over(order by loc_sid asc) rn,
        count(*) over() cnt,
        row_number() over(partition by postal_cd order by loc_sid) version
    from test t
    where city = 'Assam'
) t1
on (t1.loc_sid = t.loc_sid)
when matched then update set
    t.version = t1.version,
    t.effective_dt_from = 
        case 
            when rn = 1 then t.effective_dt_from
            else date '2017-01-01' + rn - 2
        end,
    t.effective_dt_to = 
        case 
            when rn = cnt then t.effective_dt_to
            else date '2017-01-01' + rn - 1
        end
10 rows affected

select * from test


LOC_SID
CITY
POSTAL_CD
EFFECTIVE_DT_FROM
EFFECTIVE_DT_TO
VERSION


25101
Assam
1153
01-JAN-00
01-JAN-17
1

25102
Assam
1153
01-JAN-17
02-JAN-17
2

25103
Assam
1290
02-JAN-17
03-JAN-17
1

25104
Assam
1290
03-JAN-17
04-JAN-17
2

25105
Assam
1310
04-JAN-17
05-JAN-17
1

25106
Assam
1310
05-JAN-17
06-JAN-17
2

25107
Assam
1781
06-JAN-17
07-JAN-17
1

25108
Assam
1781
07-JAN-17
08-JAN-17
2

25109
Assam
1982
08-JAN-17
09-JAN-17
1

25110
Assam
1982
09-JAN-17
31-DEC-99
2

LOC_SID	CITY	POSTAL_CD	EFFECTIVE_DT_FROM	EFFECTIVE_DT_TO	VERSION
25101	Assam	1153	01-JAN-00	01-JAN-17	1
25102	Assam	1153	01-JAN-17	02-JAN-17	2
25103	Assam	1290	02-JAN-17	03-JAN-17	1
25104	Assam	1290	03-JAN-17	04-JAN-17	2
25105	Assam	1310	04-JAN-17	05-JAN-17	1
25106	Assam	1310	05-JAN-17	06-JAN-17	2
25107	Assam	1781	06-JAN-17	07-JAN-17	1
25108	Assam	1781	07-JAN-17	08-JAN-17	2
25109	Assam	1982	08-JAN-17	09-JAN-17	1
25110	Assam	1982	09-JAN-17	31-DEC-99	2