MariaDB / MYSql non-descript syntax error with update on duplicate key

Question

I am getting a very non-descriptive error:

ERROR 1064 (42000): 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 'VALUES (order_site_id, order_id, business_email, business_domain, sm_type, activ' at line 1

Here's the insert/update

INSERT INTO table 
(
    id,
    order_site_id,
    order_id,
    business_email,
    business_domain,
    sm_type,
    active,
    code,
    sm_auth,
    installed,
    created,
    cancelled,
    uninstalled,
    updated
) VALUES (
        1,
        2,
        1,
        'jd.daniel@domain.com',
        'domain.com',
        'farce',
        'N',
        'a3yhegy',
        '55f933dad8a389cf760d39df01df923e',
        '0000-00-00 00:00:00',
        '2013-02-20 11:21:00',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00',
        '2013-02-20 11:21:00'
    ), (
        2,
        2,
        2,
        'bob.allen@domain.com',
        'domain.com',
        'farce',
        'N',
        'e4eqere',
        '08d2ab294466864d3453201be3e4a391',
        '0000-00-00 00:00:00',
        '2013-02-20 11:37:45',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00',
        '2013-02-20 11:37:45'
    ), (
        3,
        2,
        3,
        'william.grey@domain.com',
        'domain.com',
        'farce',
        'N',
        '8yme3ud',
        'c52b2cf4a6a5ceab503fe71c45d9e5da',
        '0000-00-00 00:00:00',
        '2013-02-20 11:38:28',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00',
        '2013-02-20 11:38:28'
    ), (
        4,
        2,
        4,
        'jane.doe@domain.com',
        'domain.com',
        'farce',
        'N',
        'zuzuquz',
        'c30dbfda67a0016d810b774e08e077c8',
        '0000-00-00 00:00:00',
        '2013-02-20 11:42:31',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00',
        '2013-02-20 11:42:31'
    ), (
        5,
        2,
        5,
        'john.smith@domain.com',
        'domain.com',
        'farce',
        'N',
        'nyruqu4',
        'c376194d36d5706a6ca343dc3a06248d',
        '0000-00-0000:00:00',
        '2013-02-20 11:43:29',
        '0000-00-00 00:00:00',
        '0000-00-00 00:00:00',
        '2013-02-20 11:43:29'
) ON DUPLICATE KEY UPDATE VALUES (
    order_site_id,
    order_id,
    business_email,
    business_domain,
    sm_type,
    active,
    code,
    sm_auth,
    installed,
    created,
    cancelled,
    uninstalled,
    updated
);

Bill Karwin · Accepted Answer · 2013-02-25T23:52:36.677

0

You don't use a VALUES clause with ON DUPLICATE KEY UPDATE.

The syntax should be more like UPDATE syntax:

INSERT INTO table (id, order_site_id, order_id, ...) 
VALUES (1, 2, 1, ...), (2, 2, 2, ...), ...more tuples...
ON DUPLICATE KEY UPDATE 
SET order_site_id = <expr>,
    order_id = <expr>,
    ...other column assignments...

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

If you just want the existing column values to be left alone in cases of a duplicate key, you should consider using INSERT IGNORE instead of ON DUPLICATE KEY UPDATE with a no-op update.

edited Feb 25 '13 at 23:52

answered Feb 25 '13 at 18:57

Bill Karwin

538,548
86
673
828

1

I've edited my example above to be a little more clear. REPLACE may work well for you. But keep in mind that REPLACE has some side effects. See http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570 – Bill Karwin Feb 25 '13 at 23:53

MariaDB / MYSql non-descript syntax error with update on duplicate key

1 Answers1