有則更新無則插入（mySql，oracle）

標籤：str   into   orcale   uniq   河北   div   mysq   rom   traints   

有則更新無則插入（mySql，oracle）

2018-02-01

 

1 Orcale

create table table1(id varchar2(100) primary key,name varchar2(1000),address varchar2(1000));-- 執行兩次，會報  [Err] ORA-00001: unique constraint (PBOC.SYS_C0014610) violatedinsert into table1(id,name,address)values(‘01001‘,‘影子‘,‘河北‘) ;-- 查看constraintSELECT UC.OWNER,       UC.CONSTRAINT_NAME,       UC.CONSTRAINT_TYPE,       UC.TABLE_NAME,       UCC.COLUMN_NAME,       UC.SEARCH_CONDITION,       UC.R_CONSTRAINT_NAMEFROM 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）