关于在原表中追加数据涉及的代码片段

----排序
select max(t.materialmasterpriceid) from d_ss_materialmasterprice t order by t.materialmasterpriceid desc;

---创建复制到临时表
create table d_ss_materialmasterprice12 as select * from d_ss_materialmasterprice8;

---更新临时表的id
update d_ss_materialmasterprice12 t set t.materialmasterpriceid = t.materialmasterpriceid+10000000000;

---重新拷贝一份到临时表
insert into d_ss_materialMasterPrice select * from d_ss_materialMasterPrice8 ;

update d_ss_materialmasterprice8 t set t.materialmasterpriceid = t.materialmasterpriceid+70000000 where t.materialmasterpriceid<10000000;

select max(t.materialmasterpriceid),min(t.materialmasterpriceid) from d_ss_materialmasterprice8 t;
select min(t.materialmasterpriceid) from d_ss_materialMasterPrice8 t;

select count(*) from d_ss_materialMasterPrice8;

---查id是否重复

select t.materialmasterpriceid,count(*) from d_ss_materialmasterprice8  t group by t.materialmasterpriceid having count(*) > 1

----查条数
select count(*) from (select t.materialmasterpriceid,count(*) from d_ss_materialmasterprice8 t group by t.materialmasterpriceid having count(*) > 1);

select count(*) from d_ss_materialMasterPrice;

  ----删除表
  drop table d_ss_materialmasterpricedtl purge;
  
----修改表名
ALTER TABLE d_ss_materialMasterPrice8 RENAME TO d_ss_materialMasterPrice;(大写为系统命令)

--创建价格主数据主键
-- Create/Recreate primary, unique and foreign key constraints 
alter table D_SS_MATERIALMASTERPRICE
  add constraint PK_D_SS_MATERIALMASTERPRICE primary key (MATERIALMASTERPRICEID);
  
  ----创建索引
  -- Create/Recreate indexes 
create index IX_D_SS_MATERIALMASTERPRICE1 on D_SS_MATERIALMASTERPRICE (PRICEKEY)
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IX_MATERIALMASTERPRICE1 on D_SS_MATERIALMASTERPRICE (MATERIALCODE, VENDORERPCODE, PURCHASINGGROUPCODE, CLIENTCODE)
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IX_MATERIALMASTERPRICE2 on D_SS_MATERIALMASTERPRICE (MATERIALMASTERPRICENO)
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  
  ----细单操作
  ----删除表
  drop table d_ss_materialmasterpricedtl purge;
  ------修改表名
  ALTER TABLE d_ss_materialmasterpricedtl8 RENAME TO d_ss_materialmasterpricedtl;(大写为系统命令);
  ----创建索引
  -- Create/Recreate primary, unique and foreign key constraints 
alter table D_SS_MATERIALMASTERPRICEDTL
  add constraint PK_D_SS_MASTERPRICEDTL primary key (MATERIALMASTERPRICEDTLID)
  using index 
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IX_D_SS_MATERIALMASTERPRIDTL0 on D_SS_MATERIALMASTERPRICEDTL (MATERIALMASTERPRICEID)
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
create index IX_D_SS_MATERIALMASTERPRIDTL1 on D_SS_MATERIALMASTERPRICEDTL (BATCHID)
  tablespace SRMHDL_DATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

---更新主表的id值加一个亿
update d_ss_materialmasterprice t set t.materialmasterpriceid=t.materialmasterpriceid+100000000;

---更新细表的id值加一个亿,更新细表中的主表id值加一个亿,更新细表价格为 1
update d_ss_materialmasterpricedtl t set t.materialmasterpricedtlid=t.materialmasterpricedtlid+100000000,t.materialmasterpriceid=t.materialmasterpriceid+100000000,t.taxprice=1;