9i中可以使用merge,like this: MERGE INTO table_name table1 USING table_name2 table2 ON (table1.col1=table2.col2) WHEN MATCHED THEN UPDATE SET table1.col1=table2.col2, table1.col2=table2.col3, ... WHEN NOT MATCHED THEN INSERT VALUES(table2.col1,table2.col2,table2.col3,...);
如果你的表不止这3个字段 要这样写 insert into aa(id,SCREEN_PHONE,company_id) select 1,'13555555555',2 from ( select count(*) ifexist from dual where exists ( select NULL from aa where SCREEN_PHONE ='13555555555' and COMPANY_ID=2)) a where a.ifexist=0;
MERGE INTO table_name table1
USING table_name2 table2
ON (table1.col1=table2.col2)
WHEN MATCHED THEN
UPDATE SET
table1.col1=table2.col2,
table1.col2=table2.col3,
...
WHEN NOT MATCHED THEN
INSERT VALUES(table2.col1,table2.col2,table2.col3,...);
要这样写
insert into aa(id,SCREEN_PHONE,company_id) select 1,'13555555555',2 from (
select count(*) ifexist
from dual
where exists ( select NULL from aa where SCREEN_PHONE ='13555555555' and COMPANY_ID=2)) a
where a.ifexist=0;