1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| --Old table
CREATE TABLE "WHOIS"."EPHEMERIDE"
( "DOMAIN_ID" NUMBER,
"TYPE" VARCHAR2(8),
"THE_DATE" DATE,
CONSTRAINT "EPHEMERIDE_PK" PRIMARY KEY ("DOMAIN_ID", "TYPE") VALIDATE ,
CONSTRAINT
"EP_DN_ID_TYPE_THE_DATE_UK" UNIQUE ("DOMAIN_ID", "TYPE", "THE_DATE") VALIDATE )
TABLESPACE "DATA_1"
--New table
CREATE TABLE "WHOIS"."EPHEMERIDE2" ( "DOMAIN_ID" NUMBER(38), "CREATION" DATE, "ANNIV"
DATE, "DELETION" DATE, "QUALIF" DATE, "LAST_MAJ" DATE,
PRIMARY KEY ("DOMAIN_ID") VALIDATE ) ORGANIZATION INDEX TABLESPACE "DATA_1" ;
--request Insert in the new table
insert into ephemeride2(domain_id, anniv) select domain_id, the_date from ephemeride where type='ANNIV';
update ephemeride2 ep2 set (last_maj) = (select the_date from ephemeride ep where type='LAST_MAJ' and
ep.domain_id=ep2.domain_id);
update ephemeride2 ep2 set (creation) = (select the_date from ephemeride ep where type='CREATION' and
ep.domain_id=ep2.domain_id);
update ephemeride2 ep2 set (qualif) = (select the_date from ephemeride ep where type='QUALIF' and
ep.domain_id=ep2.domain_id);
analyze table ephemeride2 compute statistics; |
Partager