试试这个: update abc_db set type='TYPE' where rowid in (select rowid from abc_db where rownum<40000);update abc_db set type='TYPE' where rownum>1000; 这个改成: update abc_db set type='TYPE' where rowid in (select rowid from (select rowid,rownum sn) where sn>1000);
update abc_db set type='TYPE' where rowid in (select rowid from (select rowid,rownum sn) where sn>1000); sn/rowid是什么?系统字段? 我试了上面2种方法,都不行。仍然是: ORA-00001: unique constraint (USER.SYS_C001315) violated
我在我这里测试没问题啊SQL> update test set col3='x' where rownum<400000;4 行 已更新
你执行以下SQL看看这个到底是什么约束:select * from user_constraints where constraint_name=UPPER('SYS_C001315') and owner=UPPER('USER');
如果以SYS登录:select * from sys.all_constraints where constraint_name=UPPER('SYS_C001315') and owner=UPPER('USER');
no rows selected 没找到。 另外我的表里有60000左右的记录。直接用update db_name set type='AB';都不行。
直接用这个看看,应该是有这个约束的: select * from sys.all_constraints where constraint_name like '%SYS_C001315%'
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS DEFERRABLE DEFERRED VALIDATED GENE RATED BAD RELY LAST_CHAN ------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- ---- ---------- --- ---- --------- USER_NAME SYS_C001315 P DB_NAME ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENE RATED NAME 18-NOV-04
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN ------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- -------------- --- ---- --------- YLY SYS_C001315 P DB_00_DZ_GZ ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 18-NOV-04应该是:CONSTRAINT_NAME :SYS_C001315
再看看这张视图:select * from all_cons_columns where constraint_name='SYS_C001315';
update db_name set type='AB'; 应该是不行的 只要表里PHONE字段有相同的数据,就会违反唯一性约束的。
OK.谢谢了!能否找出是哪些记录重复的吗?只要找出phone字段为重复的就行
奇怪的是,我用: update db_name set type='AB' where type='AL' and rownum<32001; 不行。 用下面这个再试又可以? update db_name set type='AB' where rownum<32001;
用这种方法试了下,显示0条更新,好像时间那个不起作用? update db_name set type='AB' where db_time<to_date('2004-8-1','yy-mm-dd');
用这种方法试了下,显示0条更新,好像时间那个不起作用? update db_name set type='AB' where db_time<to_date('2004-8-1','yy-mm-dd'); 应该是数据问题 看一下你的db_time
奇怪的是,我用: update db_name set type='AB' where type='AL' and rownum<32001; 不行。 用下面这个再试又可以? update db_name set type='AB' where rownum<32001;这两条语句更新的数据是不一样的,不是包含的关系
db_time是date型的字段.那两条语句都是rownum<32001啊。难道是随机排序吗?
update db_name set type='AB' where type='AL' and rownum<32001; update db_name set type='AB' where rownum<32001; 这两个数据集合相等的唯一可能是:前32000个记录中的type字段本来都是'AL' db_time是date型的字段.这个我知道,看一下db_time中的数据会不会有问题。
db_time的数据不可能有问题的。因为都是通过to_date函数转换的啊。有问题的话,不会允许写入。而且我也看了日期,然后才用的判断表达式。 另外------------- update db_name set type='AB' where type='AL' and rownum<32001; update db_name set type='AB' where rownum<32001; 这两个数据集合相等的唯一可能是:前32000个记录中的type字段本来都是'AL' --------------------我是指第一条执行报错,第二条却正常更新了。这个有点奇怪!而且我用 update db_name set type='AB' where rownum<3200; 这种形式,逐步加大rownum<3200这个数,有时候又能更新。所以很不明白。除非rownum生成的排序是随机的。
这个排序是根据rowid来的不是随机的用这个: update db_name set type='AB' where db_time<to_date('2004-08-01','yyyy-mm-dd');
仍然是:0 rows updated. 用update db_00_dz_gz set yw_type='SQ' where deal_time<=to_date('15-DEC-04','YY-MM-DD'); 倒是有反应,不过提示的是ORA-00001: unique constraint (USERNAME.SYS_C001315) violated 我还是用不同的where条件语句算了,每次能替换一部分。搞不清坨。晕:(
update abc_db set type='TYPE' where rowid in (select rowid from abc_db where rownum<40000);update abc_db set type='TYPE' where rownum>1000;
这个改成:
update abc_db set type='TYPE' where rowid in (select rowid from (select rowid,rownum sn) where sn>1000);
sn/rowid是什么?系统字段?
我试了上面2种方法,都不行。仍然是:
ORA-00001: unique constraint (USER.SYS_C001315) violated
没找到。
另外我的表里有60000左右的记录。直接用update db_name set type='AB';都不行。
select * from sys.all_constraints where constraint_name like '%SYS_C001315%'
RATED BAD RELY LAST_CHAN
------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- ----
---------- --- ---- ---------
USER_NAME SYS_C001315 P DB_NAME ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENE
RATED NAME 18-NOV-04
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- -------------- --- ---- ---------
YLY SYS_C001315 P DB_00_DZ_GZ ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 18-NOV-04应该是:CONSTRAINT_NAME :SYS_C001315
OSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
USER_NAME SYS_C001315 DB_NAME PHONE
1
USER_NAME SYS_C001315 DB_NAME TYPE
2
应该是不行的 只要表里PHONE字段有相同的数据,就会违反唯一性约束的。
update db_name set type='AB' where type='AL' and rownum<32001;
不行。
用下面这个再试又可以?
update db_name set type='AB' where rownum<32001;
update db_name set type='AB' where db_time<to_date('2004-8-1','yy-mm-dd');
update db_name set type='AB' where db_time<to_date('2004-8-1','yy-mm-dd');
应该是数据问题 看一下你的db_time
update db_name set type='AB' where type='AL' and rownum<32001;
不行。
用下面这个再试又可以?
update db_name set type='AB' where rownum<32001;这两条语句更新的数据是不一样的,不是包含的关系
update db_name set type='AB' where rownum<32001;
这两个数据集合相等的唯一可能是:前32000个记录中的type字段本来都是'AL'
db_time是date型的字段.这个我知道,看一下db_time中的数据会不会有问题。
另外-------------
update db_name set type='AB' where type='AL' and rownum<32001;
update db_name set type='AB' where rownum<32001;
这两个数据集合相等的唯一可能是:前32000个记录中的type字段本来都是'AL'
--------------------我是指第一条执行报错,第二条却正常更新了。这个有点奇怪!而且我用
update db_name set type='AB' where rownum<3200;
这种形式,逐步加大rownum<3200这个数,有时候又能更新。所以很不明白。除非rownum生成的排序是随机的。
update db_name set type='AB' where db_time<to_date('2004-08-01','yyyy-mm-dd');
用update db_00_dz_gz set yw_type='SQ' where deal_time<=to_date('15-DEC-04','YY-MM-DD');
倒是有反应,不过提示的是ORA-00001: unique constraint (USERNAME.SYS_C001315) violated
我还是用不同的where条件语句算了,每次能替换一部分。搞不清坨。晕:(