有用的SQL
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')
页:
[1]