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
Advertisement
Answer
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)