title: What's wrong with my INSERT ~ SELECT ~ ON DUPLICATE KEY query on MySql?
body: <p><BR></p>
  
  <p>Hello, everybody.</p>
  
  <p>I've made a query that use INSERT .. UPDATE .. ON DUPLICATE.</p>
  
  <p>But query that I made didn't work because syntax problem and I don't know why!</p>
  
  <p>Here's what I tried to do.</p>
  
  <p><strong>What I try to do is to give three 'magic_potion' to every account.</strong> </p>
  
  <p>First, I made two tables.</p>
  
  <pre><code>CREATE TABLE account(
      account_no INT AUTO_INCREMENT,
      account_id VARCHAR(32) NOT NULL,
      account_pw VARCHAR(40) NOT NULL,
      PRIMARY KEY(account_no)
  );
  
  CREATE TABLE item(
      item_no INT AUTO_INCREMENT,
      account_id VARCHAR(32) NOT NULL,
      item_name VARCHAR(32) NOT NULL,
      item_count SMALLINT NOT NULL,
      item_status SMALLINT NOT NULL,
      PRIMARY KEY(item_no)
  );
  </code></pre>
  
  <p>Second, I put three account for test.</p>
  
  <pre><code>INSERT INTO account (account_id, account_pw) VALUES ('James', MD5('James')), ('Andy', MD5('James')), ('Angela', MD5('James'));
  </code></pre>
  
  <p>Third, I gave magic_potion to every account.</p>
  
  <pre><code>INSERT
  INTO    item
  SELECT  NULL, A.account_id, item_name, item_count, item_status
  FROM    account A
          CROSS JOIN (SELECT 'magic_potion' AS item_name, 3 AS item_count, 1 AS item_status) B;
  </code></pre>
  
  <p>fourth, I put another account.</p>
  
  <pre><code>INSERT INTO account (account_id, account_pw) VALUES ('Judy', MD5('Judy')), ('Tom', MD5('Tom'));
  </code></pre>
  
  <p>fifth, Now, I want to give(=INSERT) two 'magic_potion' to newly added account and want to add(=UPDATE) one 'magic_potion' to previous account. So I made below query.</p>
  
  <pre><code>INSERT
  INTO    item
  SELECT  NULL, A.account_id, item_name, item_count, item_status
  FROM    account A
          CROSS JOIN (SELECT 'magic_potion' AS item_name, 3 AS item_count, 1 AS item_status) B
  ON DUPLICATE KEY UPDATE item_name = 'magic_potion', item_count = item_count + 1, item_status = 1;
  </code></pre>
  
  <p><strong>But this query didn't work. System message said,</strong></p>
  
  <pre><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 item_name = 'magic_potion', item_count = item_count + 1, item_status ' at line 6
  </code></pre>
  
  <p>I don't know what's wrong with my query.</p>
  
  <p>Please, help me.</p>
  
  <p>Thank you!</p>