CODEWITHSUNDEEP
phpmysqlsqllinux
Luke CheerfulPlum Pace
Luke CheerfulPlum Pace

Reputation: 293

MySQL ON DUPLICATE KEY UPDATE syntax error

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.

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

Upvotes: 4

Views: 14765

Answers (2)

John Woo
John Woo

Reputation: 263883

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'

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)

Upvotes: 7

tradyblix
tradyblix

Reputation: 7579

date is a reserved word, enclose it with backticks:

INSERT INTO ExtraStats (`date`, supportStaff, startEmails, etc...

Additionally, ON DUPLICATE KEY UPDATE should be something like this:

ON DUPLICATE KEY UPDATE supportStaff = '$supportStaff', startEmails = '$startEmail', etc..

You can check the manual for other examples. You should also consider saniziting the variables if you're not doing it yet to avoid SQL injections.

Upvotes: 0

Related Questions