在向表中插入数据的时候,经常遇到这样的情况:
- 1.首先判断数据是否存在
- 2.如果不存在,则插入
- 3.如果存在,则更新
SQL server
if not exists (select 1 from t where id = 1)
insert into t(id, update_time) values(1, getdate())
else
update t set update_time = getdate() where id = 1
或者
if exists (select 1 from t where id = 1)
insert into t(id, update_time) values(1, getdate())
else
update t set update_time = getdate() where id = 1
mysql
replace into 跟 insert 功能类似,不同点在于:
replace into 首先尝试插入数据到表中,如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。否则,直接插入新数据。
注意:插入数据的表必须有主键或者是唯一索引!否则的话,replace into 会直接插入数据,这将导致表中出现重复的数据。
###插入或替换
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
若id=1的记录不存在,REPLACE语句将插入新记录,否则,当前id=1的记录将被删除,然后再插入新记录。
###插入或更新
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
###插入或忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
oracle数据库:
declare num number;
begin
select count(1) into num from user_tables where table_name='ACCOUNT';
if num > 0 then
dbms_output.put_line('存在!');
execute immediate 'drop table ACCOUNT ';
end if;
execute immediate 'create table Account
(
AccountID nvarchar2(50) primary key,
AccountName nvarchar2(50)
)';
dbms_output.put_line('成功创建表!');
end;
1:隐式游标法 SQL%NOTFOUND SQL%FOUND
SQL%NOTFOUND 是SQL中的一个隐式游标,在增删查改的时候自动打开,如果有至少有一条记录受影响,都会返回false,这就就巧妙的构思出了第一种解决方案:
begin
update account set AccountName = '修改-a' where AccountID = '5';
IF SQL%NOTFOUND THEN
insert into account(AccountID,AccountName) values('5','添加-b');
END IF;
end;
先根据唯一ID到数据表中修改一条记录,如果这条记录在表中存在,则修改,并且SQL%NOTFOUND返回false。如果修改的记录不存在,SQL%NOTFOUND返回true,并且执行插入语句。
2:异常法 DUP_VAL_ON_INDEX
当Oracle语句执行时,发生了异常exception进行处理
begin
insert into account(AccountID,AccountName) values('6','添加-b');
exception
when DUP_VAL_ON_INDEX then
begin
update account set AccountName = '修改-b' where AccountID = '6';
end;
end;
当往表中插入一条数据,因为表中有主键约束,如果插入的数据在表中已经存在,则会抛出异常,在异常抛出后进行修改
3:虚拟表法 dual:
dual是一个虚拟表,用来构成select的语法规则,oracle保证dual里面永远只有一条记录
declare t_count number;
begin
select count(*) into t_count from dual where exists(select 1 from account where AccountID='11');
if t_count< 1 then
dbms_output.put_line('添加');
insert into account(AccountID,AccountName) values('11','添加-11');
else
dbms_output.put_line('修改');
update account set AccountName = '修改-11' where AccountID = '11';
end if;
end;
先声明一个变量t_count,表dual表的值赋给t_count,如果这个值小于1,表示记录不存在,进行插入操作,反之,存在就进行修改操作。
4:no_data_found法
先查找要插入的记录是否存在,存在则修改,不存在则插入。具体的实现如下:
declare t_cols number;
begin
select AccountName into t_cols from account where AccountID = '8';
exception
when no_data_found then begin
--dbms_output.put_line('添加');
insert into account(AccountID,AccountName) values('8','添加-8');
end;
when others then
begin
--dbms_output.put_line('修改');
update account set AccountName = '修改-8' where AccountID = '8';
end;
end;
5:merge法
先来看一下merge的语法,
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name SET col1 = col_val1
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
模仿
merge into Account t1
using (select '3' AccountID,'肖文博' AccountName from dual) t2
on (t1.AccountID = t2.AccountID)
when matched then
update set t1.AccountName = t2.AccountName
when not matched then
insert values (t2.AccountID, t2.AccountName);
commit;
实例应用:
merge into API_USER t1
using (select '41' partner_id,'222' account from dual)t2
on (t1.partner_id=t2.partner_id and t1.account=t2.account)
when matched then
update set
t1.mobile='22222'
where t1.partner_id=41 and t1.account='1111111'
when not matched then
insert(
t1.id,
t1.partner_id,
t1.account,
t1.name,
t1.user_type,
t1.mobile,
t1.email,
t1.company_name,
t1.bank_card,
t1.id_card,
t1.id_card_type,
t1.credit_code,
t1.contact_name,
t1.contact_phone,
t1.is_notice,
t1.is_auto_sign,
t1.sign_pwd,
t1.identify_type,
t1.identify_status,
t1.identify_mobile,
t1.user_status,
t1.remark,
t1.create_time,
t1.modify_time,
t1.stock_account,
t1.sales_depart
)
values
(
SEQ_API_USER.NEXTVAL,
41,
'1111111',
'姓名',
2,
'mobile',
'Email',
'Company_Name',
'Bank_Card',
'Id_Card',
0,
'Credit_Code',
'Contact_Name',
'Contact_Phone',
1,
1,
'identify_mobile',
1,
1,
'111111111',
1 ,
'' ,
sysdate ,
sysdate ,
'stock_account' ,
'sales_depart'
)