CODEWITHSUNDEEP
mysqlduplicatesmariadb
passion053
passion053

Reputation: 387

What's wrong with my INSERT ~ SELECT ~ ON DUPLICATE KEY query on MySql?


Hello, everybody.

I've made a query that use INSERT .. UPDATE .. ON DUPLICATE.

But query that I made didn't work because syntax problem and I don't know why!

Here's what I tried to do.

What I try to do is to give three 'magic_potion' to every account.

First, I made two tables.

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)
);

Second, I put three account for test.

INSERT INTO account (account_id, account_pw) VALUES ('James', MD5('James')), ('Andy', MD5('James')), ('Angela', MD5('James'));

Third, I gave magic_potion to every account.

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;

fourth, I put another account.

INSERT INTO account (account_id, account_pw) VALUES ('Judy', MD5('Judy')), ('Tom', MD5('Tom'));

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.

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;

But this query didn't work. System message said,

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

I don't know what's wrong with my query.

Please, help me.

Thank you!

Upvotes: 1

Views: 52

Answers (1)

elenst
elenst

Reputation: 3987

The syntax error is easy -- [CROSS] JOIN can take an optional ON clause, so the parser thinks ON DUPLICATE ... is such a clause. To avoid the syntax error, you need to enclose the SELECT in brackets:

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;

For a note, CROSS is redundant here.

Then you'll hit a context problem (ambiguous item_count). You don't need to solve it yet, because the query won't do what you expect anyway. It will never reach the ON DUPLICATE KEY UPDATE clause, because your only unique key is the primary key item_no, and you are inserting NULL into it, which means it will be always generated by auto-increment -- that is, you'll keep inserting rows every time.

There are different solutions for this, depending on what exactly you want to achieve -- you can modify the query, or you can add a UNIQUE KEY on whichever field or combination is supposed to be unique in that table (maybe it's account_id, or maybe it's a combination account_id + item_name -- it's unclear from the data sample).

Upvotes: 1

Related Questions