MySQL ON DUPLICATE KEY UPDATE语法错误 [英] MySQL ON DUPLICATE KEY UPDATE syntax error

问题描述

大家好，我想知道是否有人可以在我的sql语句中发现任何错误.如果它尚不存在，我希望将新记录插入到我的表中.如果确实存在，则只需对其进行更新.我在日期字段中的主键.

Hey guys I was wondering if anyone can spot any mistakes in my sql statement. I would like it to insert a new record into my table if one doesn't exists already. If it does exist then just update it. My primary key in the date field.

这是我在php中显示的语句，也是我得到的错误:

here is my statement as it appears in php and also the error I'm getting:

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('$startDate', '$supportStaff', '$startEmail', '$endEmail', '$emailAnswered') ON DUPLICATE KEY UPDATE (supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('$supportStaff', '$startEmail', '$endEmail', '$emailAnswered')

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('2', '3', '1', '3' at line 1 

推荐答案

INSERT INTO ExtraStats (
    DATE
    , supportStaff
    , startEmails
    , endEmails
    , emailsAnswered
    )
VALUES (
    '$startDate'
    , '$supportStaff'
    , '$startEmail'
    , '$endEmail'
    , '$emailAnswered'
    )
    ON DUPLICATE KEY

UPDATE 
        supportStaff = '$supportStaff'
        , startEmails = '$startEmail'
        , endEmails = '$endEmail'
        , emailsAnswered =  '$emailAnswered'

您还可以使用 VALUES() 函数，这样您就不会两次传递值:

You could also use the VALUES() function so you don't pass the values twice:

    ...
    ON DUPLICATE KEY    
UPDATE 
          supportStaff = VALUES(supportStaff)
        , startEmails = VALUES(startEmails)
        , endEmails = VALUES(endEmails)
        , emailsAnswered = VALUES(emailsAnswered)

这篇关于MySQL ON DUPLICATE KEY UPDATE语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！