17,377
社区成员
发帖
与我相关
我的任务
分享
mytable 表机构
id,name1,name2
--我想插入的时候,如果id=1且name1=‘张三’不存在的时候才插入
insert into mytable values(1,'张三','') where?--条件如上的时候不插入
SQL> SELECT * FROM test ;
A B
--- ----
14 0
SQL>
SQL> merge into test a
2 using (select 14 a,0 b from dual)b
3 on (a.a=b.a)
4 when not matched then insert (a.a,a.b)values(b.a,b.b);
Done
SQL> select * from test;
A B
--- ----
14 0
SQL>
SQL> SELECT * FROM test ;
A B
--- ----
14 0
SQL>
SQL> INSERT INTO test
2 SELECT 14,0 FROM dual WHERE (SELECT COUNT(*) FROM test WHERE a=14)=0
3 ;
0 rows inserted
SQL>
SQL> SELECT * FROM test ;
A B
--- ----
14 0
SQL>
SQL> INSERT INTO test
2 SELECT 14,0 FROM dual WHERE (SELECT COUNT(*) FROM test WHERE a=14)=0
3 ;
0 rows inserted
SQL>
insert into mytable values(1,'张三','')
where (select 1 from mytable where id = 1 and name = '张三')=0
insert into mytable select 1,'张三','' from dual where (select nvl(count(1),0) from mytable where name1='张三')<=0