有则更新无则插入（mySql，oracle）

table table1(id varchar2(100) primary key,name varchar2(1000),address varchar2(1000)); -- 执行两次，会报 [Err] ORA-00001: unique constraint (PBOC.SYS_C0014610) violated insert into table1(id,name,address)values(‘01001‘,‘影子‘,‘河北‘) ; -- 查看constraint SELECT UC.OWNER, UC.CONSTRAINT_NAME, UC.CONSTRAINT_TYPE, UC.TABLE_NAME, UCC.COLUMN_NAME, UC.SEARCH_CONDITION, UC.R_CONSTRAINT_NAME FROM USER_CONSTRAINTS UC INNER JOIN USER_CONS_COLUMNS UCC ON (UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME) and UC.TABLE_NAME=‘TABLE1‘; -- merge有则更新，无责插入 merge into table1 t1 using (select ‘01001‘ id,‘影子2‘ name,‘辽宁‘ address from dual) t2 on (t1.id = t2.id) when matched then update set t1.name = t2.name, t1.address = t2.address when not matched then insert values (t2.id, t2.name,t2.address); select * from table1; drop table table1;

 

2 Sql

create table table1(id varchar(100) primary key,name varchar(1000),address varchar(1000));

-- 执行两次，会报  [Err] 1062 - Duplicate entry ‘01001‘ for key ‘PRIMARY‘
insert into table1(id,name,address)values(‘01001‘,‘yingzi‘,‘hebei‘) ;

-- 无责插入（返回：受影响的行: 1），有则更改（返回：受影响的行: 2）
INSERT INTO  table1(id,name,address) VALUES (‘01001‘,‘yingzi2‘,‘hunan‘)  ON DUPLICATE KEY UPDATE name=‘yingzi2‘,address=‘hunan‘;

select * from table1;

drop table table1;

 

有则更新无则插入（mySql，oracle）

标签：str   into   orcale   uniq   河北   div   mysq   rom   traints