using ON DUPLICATE KEY UPDATE insert query for update already exist row

Question

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);          
  DataColumn[] Cols = { dt.Columns[0] };
  dt.PrimaryKey =Cols;
  DataRow crow = dt.NewRow();
  crow["CategoryID"]=1;
  crow["CategoryName"]="io";
  dt.Rows.Add(crow);
  dt.TableName = "Categories";            
  DataRow crow1 = dt.NewRow();
  crow1["CategoryID"] = 3;
  crow1["CategoryName"] = "p";
  dt.Rows.Add(crow1);
  dt.TableName = "Categories";
  catDS.Tables.Add(dt);
  return catDS;
}

I want the insert command ON DUPLICATE KEY UPDATE to update the old values with the new values. If the values does not exist then it needs to insert a new value.

It does execute, but it does not update the existing value

considering my table

1 a
2 b

Using this query

INSERT INTO categories(CategoryID,CategoryName) 
VALUES(1,qq) ON DUPLICATE KEY UPDATE CategoryID = 1

then I want the outcome to be

1 qq
2 b

score 2 · Answer 1 · answered May 28 '11 at 11:03

You're using the wrong command.

Use this query instead:

REPLACE INTO categories (CategoryID,CategoryName) 
VALUES(1,qq);

Here's why your query does not work

INSERT INTO categories(CategoryID,CategoryName) 
VALUES(1,qq) ON DUPLICATE KEY UPDATE CategoryID = 1

This code tries to insert 1 into CategoryID that doesn't work, because 1 is already in the table.
Next the ON DUPLICATE KEY clause runs.
This sets CategoryID to 1.
But is was 1 before, it's 1 after so nothing has changed and you are still trying to insert a duplicate key.

After that MySQL is out of options and gives up.

score 0 · Answer 2 · edited May 23 '17 at 12:20

You are using the right statement but in the wrong way.

Instead of

INSERT INTO categories(CategoryID,CategoryName)
VALUES(1,"qq") ON DUPLICATE KEY UPDATE CategoryID = 1

you should write

INSERT INTO categories(CategoryID,CategoryName)
VALUES(1,"qq") ON DUPLICATE KEY UPDATE CategoryName = VALUES(CategoryName)

because after the keyword UPDATE you have to put the columns that you want to change, which in your case is CategoryName. VALUES(CategoryName) means that the new value is the one that you already stated for the INSERT - which is "qq", in the example.

This is the official documentation: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

This statement is better than REPLACE INTO for several reasons. See here, for instance: Insert into a MySQL table or update if exists

using ON DUPLICATE KEY UPDATE insert query for update already exist row

2 Answers2