0

Is it possible to update only a single field with ON DUPLICATE KEY UPDATE in a table with multiple fields?

If I have a table with three fields; key, cats, dogs where key is the primary key is it possible to update a record on duplicate key, changing only one field, (for my example cats) without changing the value in the other non-key fields (dogs). This is without knowing what the value of dogs from outside of the database at the time of insertion (i.e. I have a variable holding cats value, but not one holding dogs value)

INSERT INTO `myTable` (`key`,`cats`) VALUES('someKey1','Manx') ON DUPLICATE KEY UPDATE `cats` = 'Manx';

At the moment when I run something like this and the key already exists in the table dogs is set to NULL even when it had a value previously.

James Dewes
  • 387
  • 4
  • 22
  • 1
    The behavior you describe is not correct. Only the columns in the `update` clause should be affected by this query. – Gordon Linoff Mar 23 '16 at 15:46
  • Hi Gordon, spot on thank you - you made me test my own example rather than production code and you are right. Back to the drawing board on my issue. Thank you for the feedback – James Dewes Mar 23 '16 at 16:18

2 Answers2

2

Gordon is right, it does not work the way I described. If you see this, it is not caused by the ON DUPLICATE UPDATE statement, but something else. Here is the proof:

CREATE TABLE IF NOT EXISTS `myTable` (
  `key` varchar(20) NOT NULL default '',
  `cats` varchar(20) default NULL,
  `dogs` varchar(20) default NULL,
  PRIMARY KEY  (`key`)
)

The run

INSERT INTO `myTable` (`key`, `cats`, `dogs`) VALUES
('someKey1', NULL, 'jack-russell');

Then run

INSERT INTO `myTable` (`key`,`cats`) VALUES
('someKey1','Manx') ON DUPLICATE KEY UPDATE `cats` = 'manx';

Then check the table

James Dewes
  • 387
  • 4
  • 22
1

I think you should try to UPSERT.

Please examine this:

INSERT INTO `item` (`item_name`, items_in_stock) VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE `new_items_count` = `new_items_count` + 27

MySQL UPSERT

Community
  • 1
  • 1
Hasan BINBOGA
  • 794
  • 9
  • 19