有用的SQL

sunway

1.db2 删除重复的记录：
delete from (select * from (select ENT_ROLE, ENT_COMPONENT, ENT_ALL,ENT_UPDATEDBY ,row_number() over(partition by ENT_ROLE, ENT_COMPONENT, ENT_ALL,ENT_UPDATEDBY  order by ENT_ROLE, ENT_COMPONENT, ENT_ALL,ENT_UPDATEDBY ) as row_num from BS_MOD_ROLE_ENTRY) as e where row_num >1);
delete from (select * from (select POSITION_ID, ROLE_ID ,row_number() over(partition by POSITION_ID, ROLE_ID  order by POSITION_ID, ROLE_ID ) as row_num from BS_MOD_ROLE_POSITION) as e where row_num >1);
2.征信局插入数据
  insert into "WORKBENCH"."WAS_CREDIT_BUREAU_REPORT"
  (id,update_date,lastname,firstname,sex,id_type,id_number,card1_ots_balance,card1_overdue_cycle,card1_overdue_amount)
  values
  (5,'2010-08-17','test','test','1','1','37092319780302001X',1000,2,1000);
 
  delete from "WORKBENCH"."WAS_CREDIT_BUREAU_REPORT" where id=6;
 
  update "WORKBENCH"."WAS_CREDIT_BUREAU_REPORT"
set ID_NUMBER = '411322198608030012' where id=4;

3.导入一个id为自增类型del文件---GENERATED BY DEFAULT  GENERATED ALWAYS
    AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE, MINVALUE 1, MAXVALUE 9223372036854775807, NO CYCLE, NO ORDER),
IMPORT FROM 'C:\Documents and Settings\cn02557\Desktop\BS_MOD_ROLE_ENTRY.del' OF DEL
 modified by identityignore
  COMMITCOUNT 1000
  MESSAGES 'C:\Documents and Settings\cn02557\Desktop\me.msg'
  INSERT INTO "WORKBENCH"."BS_MOD_ROLE_ENTRY";
#SYNC 10;
4.删除不符合条件的数据
delete   from "WORKBENCH"."BS_CODE_TABLE"
where id not in ('0','1')