更正一下:
--2.exists
--------------------------------
INSERT INTO PhoneTmp(Phone,RoFlag)
select distinct Phone,'R'
from PhoneR
where rownum<1000
and not exists(select null from PhoneTmp a where trim(PhoneR.Phone) = a.Phone);
----^搞错 :-)
--3.MERGE
--------------------------------
MERGE INTO PhoneTmp A
USING (SELECT distinct Phone
FROM PhoneR
WHERE rownum<=1000) B
ON (trim(A.Phone) = B.Phone)
WHEN MATCHED THEN
DO NOTHING--这里我想不做处理
--^这里不知道怎么写才能达到不处理的目的
WHEN NOT MATCHED THEN
INSERT (A.Phone, A.RoFlag)
VALUES (B.Phone, 'R');
--2.exists
--------------------------------
INSERT INTO PhoneTmp(Phone,RoFlag)
select distinct Phone,'R'
from PhoneR
where rownum<1000
and not exists(select null from PhoneTmp a where trim(PhoneR.Phone) = a.Phone);
----^搞错 :-)
--3.MERGE
--------------------------------
MERGE INTO PhoneTmp A
USING (SELECT distinct Phone
FROM PhoneR
WHERE rownum<=1000) B
ON (trim(A.Phone) = B.Phone)
WHEN MATCHED THEN
DO NOTHING--这里我想不做处理
--^这里不知道怎么写才能达到不处理的目的
WHEN NOT MATCHED THEN
INSERT (A.Phone, A.RoFlag)
VALUES (B.Phone, 'R');
我用第2种方法时得不到想要的唯一结果,出现插入重复记录的错误。
第3种方法怎么让它略过符合条件(WHEN MATCHED THEN)那一步呢。
3ks...
每次插入的是相同的纪录
INSERT INTO PhoneTmp(Phone,RoFlag)
select Phone,'R' from
(select distinct Phone,'R'
from PhoneR
and not exists(select null from PhoneTmp a where trim(PhoneR.Phone) = a.Phone)) a
where rownum<1000
;
USING (SELECT distinct Phone
FROM PhoneR
WHERE rownum<=1000) B
ON (trim(A.Phone) = B.Phone)
WHEN MATCHED THEN
SET A.phone = A.phone //这样不是一样可以
WHEN NOT MATCHED THEN
INSERT (A.Phone, A.RoFlag)
VALUES (B.Phone, 'R');
互相学习 互相提高
改成连接,或者in
那怎么用FORALL啊...
SOS,3ks...