Query syntax error: INSERT ... SELECT ... NOT IN ... ON DUPLICATE KEY UPDATE

Question

I have two test databases, db0 and bd1, respectively containing the tables t0 and t1. Each table contains the columns c0 and c1 where c0 has an unique index.

Graphically, both tables are defined as:

+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| c0      | varchar(1) | YES  | UNI | NULL    |       |
| c1      | varchar(1) | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

I want to copy rows from db0.t0 to db1.t1. The following query is instructed to:

• copy rows from t0 to t1;
• set column c1 to 123 in the copied row;
• avoid copying rows if they already exist (all columns equal) in c1;
• copy those rows which have equal key (c0) but differ otherwise.

The query is:

use db1;

insert into t1 (c0, c1)
select c0, 123 from db0.t0
where (db0.t0.c0, db0.t0.c1) not in
    (select c0, c1 from db1.t1)
on duplicate key update;

This query gives an SQL syntax error. However, removing its last line makes MySQL accept the syntax and execute the query, but it will eventually throw an error if/when it encounters a row with the same key (c0) but different otherwise (c1):

use db1;

insert into t1 (c0, c1)
select c0, 123 from db0.t0
where (db0.t0.c0, db0.t0.c1) not in
    (select c0, c1 from db1.t1);

Looking at MySQL's documentation for the INSERT ... SELECT syntax, I don't seem to be doing anything wrong.

What's going on?