INSERT ... ON DUPLICATE KEY UPDATE

[Samip Mehra]

I am using MySQL version 5.0.36 and trying to INSERT ... ON DUPLICATE KEY UPDATE and getting unexpected results. I have detailed the unexpected behavior below. I am summarizing my question here.

In the table below, (incntv_mth,mgr_empl_key) are UNIQUE while mgr_racf_id, dept_mgr_empl_key, dept_mgr_racf_id could change at any given time through a back-end process (I am trying to do this using a MySQL query as opposed to my Perl script.). All other columns are modified from the front-end and should not be affected by this INSERT query. What is the best MySQL way to insert a new record if (incntv_mth,mgr_empl_key) does not exist and update the mgr_racf_id, dept_mgr_empl_key and dept_mgr_racf_id columns if it exists and has different values for these three columns? "Run 1" below would define what I am trying to achieve. "Run 2" and "Run 3" highlight the possible unexpected behavior (It might just be a case of me missing something obvious.)

Thank you for your time.

------------------- Detailed account of the possible unexpected behavior encountered with INSERT ... ON DUPLICATE KEY UPDATE follows: -------------------
I have the following table:

CREATE TABLE `mgr_mthly_prefs` (
`incntv_mth` int(6) NOT NULL,
`mgr_empl_key` int(11) NOT NULL,
`mgr_racf_id` varchar(8) NOT NULL default 'UNKNOWN',
`dept_mgr_empl_key` int(11) NOT NULL,
`dept_mgr_racf_id` varchar(8) NOT NULL default 'UNKNOWN',
`schdle_id` int(3) NOT NULL default '0',
`call_tm` varchar(6) NOT NULL default 'XXXXXX',
`div_cde` char(3) NOT NULL default 'XXX',
`prim_call_func_cde` varchar(5) NOT NULL default 'XXXXX',
`incntv_plan_id` int(11) NOT NULL,
`call_days_adj` int(3) NOT NULL default '0',
PRIMARY KEY (`incntv_mth`,`mgr_empl_key`),
KEY `dept_mgr_key` (`incntv_mth`,`dept_mgr_empl_key`),
KEY `dept_mgr_racf` (`incntv_mth`,`mgr_racf_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I have 3 runs where I run a few INSERT queries as described below.

*** Run 1 STARTS here ***
Now I insert a column:
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'name0',222222,'name1');

Result: Table looks as expected (below). (I am keeping the columns comma-delimited for easy indentation)
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,123456,'name0',222222,'name1'

After inserting this record I run the INSERT ... ON DUPLICATE KEY UPDATE query as below.

Test 1:
INSERT into mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);

Result: (0 row(s)affected)
I am not sure if this would be different if I had only 1 unique column.

Test 2:
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,1234567,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);

Result: (1 row(s)affected)
Even though it says "1 row affected" (I am not worried about this number for now - this has also been documented), it has actually
modified both rows as below.
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,123456,'diffName0',234567,'diffName1'
200809,1234567,'diffName0',234567,'diffName1'

If I am understanding the documentation correctly, I am attributing this to the fact that if there are "2"
unique keys, the INSERT ... ON DUPLICATE KEY UPDATE query would be equivalent to an UPDATE ... WHERE
(column1=value1) OR (column2=value2). Please let me know if this is correct.
*** Run 1 ENDS here ***

*** Run 2 STARTS here ***
I first empty the mgr_mthly_prefs table.

Now I insert the same first column again:
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'name0',222222,'name1');

Result: (as expected)
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,123456,'name0',222222,'name1'

INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,1234567,'name0',222222,'name1');

Result: (as expected)
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,123456,'name0',222222,'name1'
200809,1234567,'name0',222222,'name1'

Test 1:
For values of mgr_empl_key = [123456 and 1234567], no rows were affected and the table still looks the same.
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);


Test 2:
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,12345678,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);

Result:
It has modified all rows as below. This is not always the case though as you will see in "Run 3".
Maybe, I am reading the documentation wrong.
(http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) says "If a=1 OR b=2 matches several
rows, only one row is updated." It follows to say that "In general, you should try to avoid using an ON
DUPLICATE KEY clause on tables with multiple unique indexes." Should I not be using ON DUPLICATE KEY here?
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,123456,'diffName0',234567,'diffName1'
200809,1234567,'diffName0',234567,'diffName1'
200809,12345678,'diffName0',234567,'diffName1'
*** Run 2 ENDS here ***


*** Run 3 STARTS here ***
"Run 3" is exactly the same as "Run 2" with the only the first 2 INSERT statements swapped. I am
typing out the entire scenario here just in case.

I first empty the mgr_mthly_prefs table.

Insert the first column :
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,1234567,'name0',222222,'name1');

Result: (as expected)
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,1234567,'name0',222222,'name1'

INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'name0',222222,'name1');

Result: (as expected)
incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,1234567,'name0',222222,'name1'
200809,123456,'name0',222222,'name1'

Test 1:
For values of mgr_empl_key = [123456 and 1234567], no rows were affected and the table still looks the same.
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,123456,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);


Test 2:
INSERT INTO mgr_mthly_prefs
(incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id)
VALUES (200809,12345678,'diffName0',234567,'diffName1')
ON DUPLICATE KEY UPDATE
mgr_racf_id=VALUES(mgr_racf_id),
dept_mgr_empl_key=VALUES(dept_mgr_empl_key),
dept_mgr_racf_id=VALUES(dept_mgr_racf_id);

Result:
Unexpected behavior:
It has NOT modified any of the existing columns as you can see in the results below. It is not necessary
that I perform the initial inserts in the order above. Sometimes, "Run 2" gives the same unexpected
behavior.

What am I missing here? Could you please tell me how to use INSERT ... ON DUPLICATE KEY UPDATE
for my simple scenario here? If it is not the best option, what would be the MySQL recommended option?

incntv_mth,mgr_empl_key,mgr_racf_id,dept_mgr_empl_key,dept_mgr_racf_id
200809,1234567,'name0',222222,'name1'
200809,123456,'name0',222222,'name1'
200809,12345678,'diffName0',234567,'diffName1'
*** Run 3 ENDS here ***