I know it has been a long time between drinks/posts, but I've been busy -- I promise! :)

Today I spent a considerable amount of time trying to figure out why an INSERT SELECT ON DUPLICATE KEY UPDATE was not behaving as I would expect.

Here is an example to illustrate:

CREATE TABLE t1 (
  id INT AUTO_INCREMENT,
  num INT NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id INT NOT NULL,
  num INT
);

INSERT INTO t1 VALUES (1, 10);
INSERT INTO t2 VALUES (1, NULL);


INSERT INTO t1
  SELECT id, num
  FROM t2
ON DUPLICATE KEY UPDATE
  num=IFNULL(VALUES(num), t1.num);

To convert the above query into plain English -- I'm saying, INSERT into table t1 the id and num …