Error 1064 when using SQL with INSERT INTO … SELECT … ON DUPLICATE KEY UPDATE

I am trying to use ‘INSERT INTO … SELECT ON DUPLICATE KEY UPDATE feature’ but I’m in trouble now.

I want to insert data to ‘fruitProperty’ table.

My query is as below:

START TRANSACTION;

SET @myVal1 := "";
SET @myVal2 := 0;
SET @myVal3 := 0;
SET @myVal4 := 0;
SET @myVal5 := 0;

SELECT masterIndex INTO @myVal1 FROM fruitMaster WHERE masterName = 'apple';
SELECT masterIndex INTO @myVal2 FROM fruitMaster WHERE masterName = 'banana';
SELECT masterIndex INTO @myVal3 FROM fruitMaster WHERE masterName = 'mango';
SELECT masterIndex INTO @myVal4 FROM fruitMaster WHERE masterName = 'melon';
SELECT masterIndex INTO @myVal5 FROM fruitMaster WHERE masterName = 'grape';

INSERT
INTO    fruitProperty
        (fruitID, masterIndex, cpValue)
SELECT  A1.fruitID, A2.masterIndex, A2.cpValue
FROM    (   
           SELECT   A.fruitID
           FROM     fruit A
                    JOIN fruitProperty B ON A.fruitID = B.fruitID
           WHERE    B.masterIndex = @myVal1 AND B.cpValue = 1
        ) A1
        CROSS JOIN
        (
           SELECT @myVal2 AS masterIndex, 1 AS cpValue
           UNION
           SELECT @myVal3, 1
           UNION
           SELECT @myVal4, 1
           UNION
           SELECT @myVal5, 1
       ) A2
ON DUPLICATE KEY UPDATE cpValue = cpValue + 1;

ROLLBACK;

I met an error code.

error code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘KEY UPDATE cpValue = 1’ at line 21

What’s wrong with my query? I really don’t know..

Thank you.

Answer

Do you get a problem if you use an explicit join:

INSERT INTO fruitProperty (fruitID, masterIndex, cpValue)
SELECT f.fruitID, A2.masterIndex, A2.cpValue
FROM (SELECT f.fruitID
      FROM fruit f JOIN
           fruitProperty fp
            ON f.fruitID = fp.fruitID
      WHERE f.masterIndex = @myVal1 AND fp.cpValue = 1
     ) f JOIN
     (SELECT @myVal2 AS masterIndex, 1 AS cpValue
      UNION ALL
      SELECT @myVal3, 1
      UNION ALL
      SELECT @myVal4, 1
      UNION ALL
      SELECT @myVal5, 1
     ) A2
     ON 1=1
ON DUPLICATE KEY UPDATE cpValue = VALUES(cpValue) + 1;

I suspect the problem is a parsing problem, because MySQL/MariaDB supports the ON clause for CROSS JOIN (yuck!!!). But the ON keyword gets confused.