详情见 :http://topic.csdn.net/u/20100126/14/8e77718b-1b3e-4c11-b195-29f53ba1444a.html上面改后还有一个问题:就是会报
create table SITES_INFO
(
MAIN_DOMAIN VARCHAR2(160),
HOST_NAME VARCHAR2(160) not null,
OUT_LINKS NUMBER,
LINKS NUMBER,
STATUS NUMBER default 40,
ALLCOUNT NUMBER,
FAILEDCOUNT NUMBER,
CREATE_DATE DATE default sysdate,
LAST_UPDATE_DATE DATE default sysdate,
CLIENT_ID VARCHAR2(150),
NUM1 NUMBER,
NUM2 NUMBER,
STR1 VARCHAR2(150),
STR2 VARCHAR2(150),
STR3 VARCHAR2(150),
GRADE NUMBER,
INTERVALDAYS NUMBER,
SITE_LANGUAGE_TYPE NUMBER default 0
)create table DOMAIN_ALEXA_INFO
(
MAIN_DOMAIN VARCHAR2(160) not null,
ALEXA_RANKING NUMBER,
CHINAALEXARANK NUMBER,
STATUS NUMBER default 40,
SITELANGUAGETYPE NUMBER default 0,
SITEOUTLINKS NUMBER,
SITEONLINEDATE DATE,
USERCOUNTRYINFO VARCHAR2(300),
SITEAVGSPEEDINFO VARCHAR2(300),
ATTEMPTTIMES NUMBER,
CREATE_DATE DATE default sysdate,
LAST_UPDATE_DATE DATE default sysdate,
NETGY_GRADE NUMBER,
INTERVAL_VISIT_DAYS NUMBER,
CLIENT_ID VARCHAR2(150),
NUM1 NUMBER,
NUM2 NUMBER,
STR1 VARCHAR2(150),
STR2 VARCHAR2(150),
STR3 VARCHAR2(150)
)alter table DOMAIN_ALEXA_INFO
add constraint DOMAIN_ALEXA_INFO__MAIN_DOMAIN primary key (MAIN_DOMAIN)
为什么会出现报 ora-00001 违反唯一 约束条件 DOMAIN_ALEXA_INFO__MAIN_DOMAIN
如果domain_alexa_info 存在main_domain时,就更新时间,只有不存在时,才会插入的啊
怎么会违反违反唯一 约束条件
要怎么改sql
create table SITES_INFO
(
MAIN_DOMAIN VARCHAR2(160),
HOST_NAME VARCHAR2(160) not null,
OUT_LINKS NUMBER,
LINKS NUMBER,
STATUS NUMBER default 40,
ALLCOUNT NUMBER,
FAILEDCOUNT NUMBER,
CREATE_DATE DATE default sysdate,
LAST_UPDATE_DATE DATE default sysdate,
CLIENT_ID VARCHAR2(150),
NUM1 NUMBER,
NUM2 NUMBER,
STR1 VARCHAR2(150),
STR2 VARCHAR2(150),
STR3 VARCHAR2(150),
GRADE NUMBER,
INTERVALDAYS NUMBER,
SITE_LANGUAGE_TYPE NUMBER default 0
)create table DOMAIN_ALEXA_INFO
(
MAIN_DOMAIN VARCHAR2(160) not null,
ALEXA_RANKING NUMBER,
CHINAALEXARANK NUMBER,
STATUS NUMBER default 40,
SITELANGUAGETYPE NUMBER default 0,
SITEOUTLINKS NUMBER,
SITEONLINEDATE DATE,
USERCOUNTRYINFO VARCHAR2(300),
SITEAVGSPEEDINFO VARCHAR2(300),
ATTEMPTTIMES NUMBER,
CREATE_DATE DATE default sysdate,
LAST_UPDATE_DATE DATE default sysdate,
NETGY_GRADE NUMBER,
INTERVAL_VISIT_DAYS NUMBER,
CLIENT_ID VARCHAR2(150),
NUM1 NUMBER,
NUM2 NUMBER,
STR1 VARCHAR2(150),
STR2 VARCHAR2(150),
STR3 VARCHAR2(150)
)alter table DOMAIN_ALEXA_INFO
add constraint DOMAIN_ALEXA_INFO__MAIN_DOMAIN primary key (MAIN_DOMAIN)
为什么会出现报 ora-00001 违反唯一 约束条件 DOMAIN_ALEXA_INFO__MAIN_DOMAIN
如果domain_alexa_info 存在main_domain时,就更新时间,只有不存在时,才会插入的啊
怎么会违反违反唯一 约束条件
要怎么改sql
MERGE INTO DOMAIN_ALEXA_INFO alexa
USING (select main_domain, CLIENT_ID
from sites_info
where status = 40
and CLIENT_ID = 'default') sites
on (alexa.main_domain = sites.main_domain)
WHEN MATCHED THEN
UPDATE set alexa.CREATE_DATE = sysdate
WHEN NOT MATCHED THEN
INSERT (main_domain, CLIENT_ID) values (sites.main_domain, 'default')
select main_domain, CLIENT_ID,count(1)over(partition by main_domain)c
from sites_info
where status = 40
and CLIENT_ID = 'default')
where c>1看看main_domain字段是否有重复。因为sites中的main_domain字段没有唯一约束,很可能是这个问题
select main_domain, CLIENT_ID
from sites_info
where status = 40
and CLIENT_ID = 'default'
产生了重复数据,造成违反唯一性
上面语句执行结果 不止一条记录我改成这样:MERGE INTO DOMAIN_ALEXA_INFO alexa
USING (select main_domain, CLIENT_ID
from (select main_domain,
CLIENT_ID,
rownum() over(partiton by main_domain order by main_doamin) rn
from sites_info
where status = 40
and CLIENT_ID = 'default')
where rn = 1) sites
on (alexa.main_domain = sites.main_domain)
WHEN MATCHED THEN
UPDATE set alexa.CREATE_DATE = sysdate
WHEN NOT MATCHED THEN
INSERT (main_domain, CLIENT_ID) values (sites.main_domain, 'default')
可以这里出现了 没有找到form 关键字错
难道9iR2不支持row_number()吗
CLIENT_ID
--rownum() over(partiton by main_domain order by main_doamin) rn
from sites_info
where status = 40
可以成功执行但这样就不行:
select main_domain,
CLIENT_ID,
rownum() over(partiton by main_domain order by main_doamin) rn
from sites_info
where status = 40
这样就报找不到预期的from 关键字错 有什么变通的方法解决
USING (select main_domain, CLIENT_ID
from (select main_domain,
CLIENT_ID,
row_number() over(partition by main_domain order by main_domain) rn
from sites_info
where status = 40
and CLIENT_ID = 'default')
where rn = 1) sites
on (alexa.main_domain = sites.main_domain)
WHEN MATCHED THEN
UPDATE set alexa.CREATE_DATE = sysdate
WHEN NOT MATCHED THEN
INSERT (main_domain, CLIENT_ID) values (sites.main_domain, 'default')a
执行成功
谢谢 wildwave
hebo2005
我怎么就没发现问题在哪,是做得太少了,还是太笨了,值得反思下!呵呵