Mysql – on duplicate key update

MySQL

I'm trying to execute the following statement in MySQL:

  INSERT IGNORE INTO shop_item_details (shop_id,product_id,product_name,Product_model,Product_category,Product_details,price) 
VALUES (1, NULL, 'camera', 'sony','', 'hd', '5000') 
ON DUPLICATE KEY 
UPDATE (shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_category='', Product_details='hd',price='5000')

But it produces an error:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(shop_id='1',product_id='',product_name='camera', Product_model='sony',Product_c' at line 1

What is wrong with my query?

Best Answer

Community Wiki answer originally based on notes by ypercubeᵀᴹ and Rick James suggested in the comments on the question.

The immediate issue is the parentheses around the UPDATE set list: they should not be there according to the syntax. That is what the error message is about.

Once you have fixed that one, there are other issues with your INSERT statement.

  • The IGNORE keyword just does not make sense when used together with ON DUPLICATE KEY. You either perform an update when a duplicate key is encountered, or you ignore the duplicate key entry completely, and it looks as though you want the former – therefore, lose the IGNORE keyword.

  • Setting product_id to '' in the ON DUPLICATE KEY clause is not consistent with setting it to NULL in the VALUES clause: an empty string ('') is not the same as a null. Judging from the column name, you probably want to have NULL specified in both places.

  • Similarly, seeting id to '1' in the ON DUPLICATE KEY clause is not consistent with setting it to 1 in the VALUES clause: the string ('1') is not the same as the number (1). This will probably not be an issue as the string will be converted to a number but it doesn't hurt to be explicit and very careful with the types of your column and values you pass to them.

  • While this is not exactly a problem, you don't have to specify the same values twice (in VALUES and in ON DUPLICATE KEY UPDATE clauses). You can specify them once and use them in two places, using the VALUES() function, like this:

    INSERT INTO shop_item_details 
  (shop_id, product_id, product_name, Product_model, 
   Product_category, Product_details, price) 
VALUES 
  (1, NULL, 'camera', 'sony', '', 'hd', '5000') 
ON DUPLICATE KEY UPDATE
  shop_id          = VALUES(shop_id), 
  product_id       = VALUES(product_id), 
  product_name     = VALUES(product_name), 
  Product_model    = VALUES(Product_model), 
  Product_category = VALUES(Product_category), 
  Product_details  = VALUES(Product_details), 
  price            = VALUES(price) ;

    Please note that the above is just an example. Since you are updating the same set of columns as those you are inserting into, setting each of them using the column = VALUES(column) pattern is slightly redundant. The columns that define the unique (or primary) key being violated (you have not specified which columns they are in your case - perhaps shop_id and product_id? Or shop_id and product_name and product_model?) can be safely removed from the ON DUPLICATE KEY UPDATE clause.

Related Question