SqlServer/Oracle 通过一个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.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,‘重庆‘)  

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!