MySQL、SqlServer和Oracle 数据库 Update 存在则更新,不存在则插入on duplicate key update

SQL数据库如下:

DECLARE @isexist INT--申明变量

            select @isexist=count(*) from Table1  where ip=@ip
            if(@isexist>0)
           update Table1  set ConnTimes=ConnTimes+@ConnTimes,Connecting=@Connecting
           where  ip=@ip
            else
           insert into Table1  (Name,Descriptions,ip,ConnTimes,Connecting)values

           (@Name,@Descriptions,@ip,1,1)


Oracle如下:           

MERGE INTO MM_FREZENDPRESALEORDER T1
USING (SELECT '990000006693' AS ordercode,'a' AS salecode,'b' as warecode,1 as frezenqty  FROM dual) T2
ON ( T1.ordercode=T2.ordercode and T1.warecode=T2.warecode and T1.salecode=T2.salecode)
WHEN MATCHED THEN
    UPDATE SET T1.frezenqty = T1.frezenqty + T2.frezenqty
WHEN NOT MATCHED THEN 
    INSERT (ID,ordercode,warecode,salecode,createdate,frezenqty) VALUES(SYS_GUID(),T2.ordercode,T2.warecode,T2.salecode,sysdate,T2.frezenqty);

    


最近做MySQL,例子如下:

 insert into  pet_store.logintest(Id, loginName, userName, passWord, createDate, Creator, modifyDate, Modifier, Remark)
            select @Id as Id ,@loginName as loginName,@userName as userName, @passWord as passWord, @createDate as createDate,
            @Creator as Creator,@modifyDate as modifyDate, @Modifier as Modifier, @Remark as Remark from dual 
            on duplicate key update loginName=values(loginName),userName=values(userName),passWord=values(passWord),
            modifyDate=values(modifyDate),Modifier=values(Modifier),Remark=values(Remark)

    
    
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yitonglizihao/article/details/49943057
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

MySQL、SqlServer和Oracle 数据库 Update 存在则更新,不存在则插入on duplicate key update

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭