merge into table t1
using table t2 on (t2.id=t1.id and t1.id=iID)
when matched then update set t1.other=sOther
when not matched then insert (t1.id,t1.Other)
values(iID,sOther)
这样不知道可不可以?
create procedure UpdateorInsert (Table_Id:long,Table_other:long)
as
begin
delete from Table where Id = Table_Id ;
insert into Table (Id,Other) values (Table_Id,Table_Other);
end;
Create Or Replace Procedure P_TEST(Varchar2 V_ID,Varchar2 V_OTHER)Return Number Is
v_count Integer;
Begin
Select ID
Into V_COUNT
From TABLENAME
Where ID=V_ID;
If V_COUNT<>0 Then
Update TABLENAME Set OTHER=V_OTHER
Where ID=V_ID;
Else
Insert Into TABLENAME(ID,OTHER)Values(V_ID,V_OTHER);
End If;
Return V_COUNT;
End P_TEST;
create proc updateOrInsert @inputId integer
As
if exists(select id from tablename where id=@inputId)
BEGIN
update tablename set other=@inputId*100+@inputId*10+@inputId
where id=@inputId
END
else
BEGIN
insert into tablename (id, other) values(@inputId,@inputId*100+@inputId*10+@inputId)
END