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
