What is the correct syntax for INSERT INTO ... ON DUPLICATE KEY UPDATE in MySQL?

Question

my table(s) structure (MySQL / each one is same as below)

+-------+--------------+------+------+-------------------+
| Field | Type         | Null | Key  | Default           |
+-------+--------------+------+------+-------------------+
| id    | int(11)      | NO   | PRI  | AUTO INCREMENT    | 
| lesson| varchar(255) | NO   |      | LESSON_NAME       | 
| exam  | char(50)     | NO   |UNIQUE| NO DEFAULT        |
| quest | text         | NO   |      | NO DEFAULT        |
| answer| text         | NO   |      | NO DEFAULT        |
| note  | text         | NO   |      | NO DEFAULT        |
+-------+--------------+------+------+-------------------+

and i'am posting some values to add this table via ajax ($post) - PHP 5.0
in database.php there is a function to get posted data and add to table

function update_table ($proper_table, $name, $question, $answer, $note) {
$sql = "INSERT INTO $proper_table (id, lesson, exam, quest, answer, note) VALUES ('', '', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";
$result= mysql_query($sql)or die(mysql_error());
}

$proper_table variable is taken by another variable to add this record to correct table.
(NOTE: Original table fields and variables are different (Turkish), to be more understandable i traslated to english but the syntax is the same as you see.)
Question : I want to check that if there is a record that exam field is same then all these variables will be used for updating this record, otherwise let function put this record to proper table as a new record.
But i'am getting error like below

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

is there any faulty coding? and what can be the solution?
Thanks right now...

I dont think thats the entire error message. Also use prepared statements instead (see documentation for the mysqli extension) — Vitamin, Feb 20 '12 at 20:17
or at least put the values in quotes ('). please provide the values for the variables involved or the final query-string (without variables). — Basti, Feb 20 '12 at 20:20

score 6 · Accepted Answer · edited May 14 '20 at 20:52

function update_table ($proper_table, $name, $question, $answer, $note) {
    $sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', '$name', '$question','$answer','$note') ON DUPLICATE KEY UPDATE quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";
   $result= mysql_query($sql)or die(mysql_error());
}

Just breaking this out I'll detail the changes

$sql = "INSERT INTO $proper_table 

// Removed the PK (primary key) AI (auto increment) field - don't need to specify this
(lesson, exam, quest, answer, note)     

// Likewise removed PK field, and added quotes around the text fields
VALUES ('', '$name', '$question','$answer','$note')    
ON DUPLICATE KEY UPDATE 

// If you specify VALUES(fieldName) it will update with the value you specified for the field in the conflicting row
// Also removed the exam update, as exam is the UNIQUE key which could cause conflicts so updating that would have no effect
quest = VALUES(quest), answer = VALUES(answer), note = VALUES(note)";

score 0 · Answer 2 · answered Feb 20 '12 at 20:20

0

You need to wrap you string variables in single quotes in your SQL '$name' for example. Otherwise mysql thinks you are referencing column names.

answered Feb 20 '12 at 20:20

Brian Glaz

15,468
4
37
55

score 0 · Answer 3 · answered Feb 20 '12 at 20:27

With that query, when you add ON DUPLICATE KEY UPDATE... it will update when the id es the same than the id that you are sending, in this case you are not sending an id as parameter so it will never update because you have the id with auto-increment.

A solution could be that you read the table where exam equals the parameter you are sending, something like this:

    SELECT id FROM $proper_table;

If it is null the you execute an insert, if it is not null the you update taking as parameter the id that you are getting from the select

exam is a UNIQUE key and so there can be conflicts – Simon at The Access Group Feb 20 '12 at 20:28 — Simon at The Access Group, Feb 20 '12 at 20:28

score 0 · Answer 4 · answered Feb 20 '12 at 20:29

id auto-increments, so presumably you don't want to set an empty string as id.

Try:

$sql = "INSERT INTO $proper_table (lesson, exam, quest, answer, note) VALUES ('', $name, $question,$answer,$note) ON DUPLICATE KEY UPDATE exam = $name, quest = $question, answer = $answer, note = $note";

score 0 · Answer 5 · answered Feb 20 '12 at 20:34

You have to make it like this

<?php
function update_table($proper_table, $name, $question, $answer, $note, $id) {

    $sqlQuery = "INSERT INTO '".$proper_table."' SET
                    name        =       '".$name."',
                    question        =   '".$question."',
                    answer      =       '".$answer."',
                    note        =       '".$note."' WHERE id = '".$id."'";

    $result= mysql_query($sqlQuery)or die(mysql_error());

}
?>

What is the correct syntax for INSERT INTO ... ON DUPLICATE KEY UPDATE in MySQL?

5 Answers5