SqlServerOracle 通过一个sql判断新增修改

概述if (Config.DbInfo.DbType.Equals(DBType.SQLServer)){ sql = " IF EXISTS (SELECT 1 FROM wifi.imsi_model_status WHERE device_id = @device_id and wireless=‘" + row[0].GetString() + "‘) UPDATE wifi.

if (Config.DbInfo.DbType.Equals(DBType.sqlServer)){    sql = " IF EXISTS (SELECT 1 FROM wifi.imsi_model_status WHERE  device_ID = @device_ID and wireless=‘" + row[0].GetString() + "‘) UPDATE wifi.imsi_model_status SET model_status = @model_status,[email protected],[email protected],Log_Time=" + SysConst.time_sqlserver + " WHERE device_ID = @device_ID and wireless=‘" + row[0].GetString() + "‘ ELSE INSERT INTO wifi.imsi_model_status(Log_Time,device_ID,wireless,model_status,version,serialnumber) VALUES(" + SysConst.time_sqlserver + ",@device_ID,‘" + row[0].GetString() + "‘,@model_status,@version,@serialnumber)";    cmdParms = new IDataParameter[4];    cmdParms[0] = new sqlParameter("@device_ID",deviceid);    cmdParms[1] = new sqlParameter("@model_status",row[1].GetString());    cmdParms[2] = new sqlParameter("@version",ver);    cmdParms[3] = new sqlParameter("@serialnumber",ser);    sqlDic.Add(sql,cmdParms);}else if (Config.DbInfo.DbType.Equals(DBType.Oracle)){    sql = "MERGE INTO wifi.imsi_model_status a USING (select count(*) co from wifi.imsi_model_status WHERE  device_ID = :device_ID and wireless=‘" + row[0].GetString() + "‘) b  ON (b.co<>0) WHEN MATCHED THEN UPDATE  SET Log_Time=" + SysConst.time_sql + ",model_status = :model_status,version=:version,serialnumber=:serialnumber WHERE  device_ID = :device_ID and wireless=‘" + row[0].GetString() + "‘ WHEN NOT MATCHED THEN INSERT (Log_Time,serialnumber) VALUES(" + SysConst.time_sql + ",:device_ID,:model_status,:version,:serialnumber)";    cmdParms = new IDataParameter[4];    cmdParms[0] = new OracleParameter(":device_ID",deviceid);    cmdParms[1] = new OracleParameter(":model_status",row[1].GetString());    cmdParms[2] = new OracleParameter(":version",ver);    cmdParms[3] = new OracleParameter(":serialnumber",cmdParms);}

代码很简单：

1.sqlserver就是使用的exists关键字判断

2.oracle这边就是使用的merge关键字判断

 

sqlserver核心代码： 

if exists (select 1 from Teacher where ID=‘1‘)    update Teacher set name=‘lisi‘,addr=‘重庆‘ where ID=‘1‘else    insert into Teacher(ID,name,addr) values(1,‘lisi‘,‘重庆‘)

 

 Oracle核心代码： 

MERGE INTO Teacher USING (select count(1) co from Teacher WHERE ID=‘1‘) on(co<>0)WHEN MATCHED THEN  update set name=‘lisi‘,addr=‘重庆‘ where ID=‘1‘WHEN NOT MATCHED THEN  insert (ID,‘重庆‘)  

总结

以上是内存溢出为你收集整理的SqlServer/Oracle 通过一个sql判断新增/修改全部内容，希望文章能够帮你解决SqlServer/Oracle 通过一个sql判断新增/修改所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。