插入数据语句如下:
insert into tableA
(id, i_id,some_id,selfcolumn)
select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..'如何在执行该语句时,判断数据(select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..')是否已经存在呢?本人试过用:
insert into tableA
(id, i_id,some_id,selfcolumn)
select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..' and not exists ( select * from
tableA ta
join
tableB tb
on ( ta.id= tb.id)
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..' )感觉逻辑上不太对。。求助。
insert into tableA
(id, i_id,some_id,selfcolumn)
select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..'如何在执行该语句时,判断数据(select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..')是否已经存在呢?本人试过用:
insert into tableA
(id, i_id,some_id,selfcolumn)
select sd_cust_pk_seq.nextval,
tb.id,
tc.id,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..' and not exists ( select * from
tableA ta
join
tableB tb
on ( ta.id= tb.id)
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..' )感觉逻辑上不太对。。求助。
merge into tableA
(id, i_id,some_id,selfcolumn)
USING(select sd_cust_pk_seq.NEXTVAL id1,
tb.id id2,
tc.id id3,
mydata
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..' ) tableB
ON (tableA.id=tableB.id1 AND tableA.i_id=tableB.id2 AND tableA.some_id=tableB.id3)
WHEN NOT matched THEN
INSERT VALUES(tableB.id1 ,tableB.id2,tableB.id3,tableB.mydata) ;
insert into SD_CUST_&&NN._HOST.impl_lic_app_ftr
(ID, IMPL_ID, APP_FTR_ID, LIC_START_DATE, LIC_END_DATE, DATE_CREATED, CREATED_BY)
select SD_CUST_&&NN._HOST.sd_cust_pk_seq.nextval as v_id,
i_id,
af_id,
sysdate,
sysdate + 100000,
sysdate,
'Bug_10329_part2.sql' from
(select i.id as i_id, af.id af_id
from SD_CUST_&&NN._HOST.impl i
cross join SD_CUST_&&NN._HOST.app_ftr af
where i.code = 'SDMD'
and af.code = 'perm-app-config-write') tmp
left join SD_CUST_&&NN._HOST.impl_lic_app_ftr ilaf
on tmp.i_id = ilaf.impl_id
and
tmp.af_id = ilaf.app_ftr_id
and ilaf.impl_id is null;
with t as (
select tb.bid, tc.cid, mydata
from tableB tb cross join tableC tc
where tb.column?= '..' and tc.column?= '..'
)
insert into tableA (id, i_id,some_id,selfcolumn)
select sd_cust_pk_seq.nextval, t.* from t
where not exist (select * from tableA
where t.bid=i_id and t.bid=some_id and t.mydata=selfcolumn);
MERGE INTO TABLEA a --要插入数据的表
using (select sd_cust_pk_seq.nextval seqid,
tb.id i_id,tc.id some_id,mydata selfcolumn
from tableB tb
cross join tableC tc
where tb.column?= '..'
and tc.column?= '..') t --要插入的表
on(t.seqid=a.id)--连接条件
insert
values (t.seqid, t.i_id,t.some_id,t.selfcolumn)