ON DUPLICATE KEY UPDATE插入查询以进行更新已存在行 [英] ON DUPLICATE KEY UPDATE insert query for update already exist row

问题描述

  connection()
{
      OleDbConnection nwindConn=new OleDbConnection();
            MySql.Data.MySqlClient.MySqlConnection con= new MySqlConnection();
            MySqlCommand cmd;
            con.ConnectionString ="server=localhost;" +
                                   "uid=root;"+
                                   "pwd=;" +
                                   "database=globasys;" ;
            DateTime dt=DateTime.Now;
          string select = "SELECT CategoryID, CategoryName FROM categories";
            MySqlDataAdapter catDA = new MySqlDataAdapter(select, con);
            string insert = "insert into categories(CategoryID,CategoryName) VALUES(@CategoryID,@CategoryName)ON DUPLICATE KEY UPDATE CategoryID=@CategoryID";

=

            catDA.InsertCommand = new MySqlCommand(insert, con);
            catDA.InsertCommand.Parameters.Add("@CategoryID", MySqlDbType.Int32, 11, "CategoryID");
            catDA.InsertCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 250, "CategoryName");

            DataSet catDS = new DataSet();
            catDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            catDA.Fill(catDS, "Categories");
            DataSet newdt = new DataSet();
            newdt = getnewdata();
           int i= catDA.Update(newdt, "Categories");
        }

        public DataSet getnewdata()
        {
           DataSet catDS=new DataSet();
           DataTable dt = new DataTable();
            DataColumn col1 = new DataColumn("CategoryID", typeof(int));
            dt.Columns.Add(col1);
                     DataColumn col=new      DataColumn("CategoryName",typeof(string));
            dt.Columns.Add(col);
            //catDS.Tables[0].Columns.Add(col);
            DataColumn[] Cols = { dt.Columns[0] };
            dt.PrimaryKey =Cols;
            DataRow crow = dt.NewRow();
                crow["CategoryID"]=1;
                crow["CategoryName"]="ffg";
                dt.Rows.Add(crow);
                dt.TableName = "Categories";
               DataRow crow1 = dt.NewRow();
                crow1["CategoryID"] = 3;
                crow1["CategoryName"] = "tejfgfdashri";
                dt.Rows.Add(crow1);
                dt.TableName = "Categories";
                catDS.Tables.Add(dt);
            return catDS;
        }

我将使用ON DUPLICATE KEY UPDATE插入查询来更新已经存在的更新，但是它将执行catda.update(newdt)，它将返回2行受影响，但表值未更改，它将保持原样. br/> 预先装上坦克

i will be used the ON DUPLICATE KEY UPDATE insert query for update the already exist but it will be execute the catda.update(newdt) it will be return 2 row affected but table values are not changed it will be remains as it is.
tanks in advance

推荐答案

列 CategoryID 或 CategoryName 被标记为UNIQUE或MySql中的PRIMARY KEY?
如果不是，那么INSERT ... ON DUPLICATE KEY UPDATE将无法按您预期的方式运行.

请参阅 INSERT ...关于重复键更新语法 [ ^ ]

尝试自己执行INSERT语句，看看会发生什么.

Is column CategoryID or CategoryName marked as UNIQUE or PRIMARY KEY in MySql ?
If not, then INSERT ... ON DUPLICATE KEY UPDATE will not works as you expected.

Please see INSERT ... ON DUPLICATE KEY UPDATE Syntax[^]

Try to execute the INSERT statement your self and see what happens.

这篇关于ON DUPLICATE KEY UPDATE插入查询以进行更新已存在行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！