CODEWITHSUNDEEP
mysql
Keiran Lovett
Keiran Lovett

Reputation: 604

ON DUPLICATE KEY still duplicating inserts

I have an issue where instead of checking to see if there's a duplicate, the script below just inserts the row into mysql, no updating at all. I've tried again and again yet there's always duplication going on.

What exactly am I doing wrong here? 

if ($completeStatus == "0") {

// Get the default questions responses and insert into database
$questionsAnswered = $_GET['questions'];
foreach( $questionsAnswered as $key => $answers){
    $query = "INSERT INTO survey_ResponseDetail (responseHeader,
                        questionID,
                        questionText,
                        ansLikelihood,
                        ansExpConsq,
                        ansRepRisk,
                        currWayMitigate
                    )
VALUES ('$ResponseHeader',
        '$key',
        'test1',
        '$answers[1]',
        '$answers[2]',
        '$answers[3]',
        '$answers[4]'
        )
ON DUPLICATE KEY UPDATE ansLikelihood = '$answers[1]',
                        ansExpConsq = '$answers[2]',
                        ansRepRisk = '$answers[3]',
                        currWayMitigate = '$answers[4]',
                        id = LAST_INSERT_ID(id)";
    $dbresult = mysql_query($query) or die ('Database Error (' . mysql_errno() . ') ' . mysql_error());

}

Upvotes: 0

Views: 360

Answers (2)

JohnFx
JohnFx

Reputation: 34909

Assuming your assertion in the comments is correct, the problem is that you haven't defined a unique constraint or primary key on any fields in survey_ResponseDetail.

Per the MySQL Documentaiton on the ON DUPLICATE KEY UPDATE syntax

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

If you don't designate a field as unique (or indirectly doing so by marking it as a PK) then MySQL has no way of knowing what a duplicate key is. Thus the command isn't doing what you expect.

If you tell me which field (or combination of fields) must be unique for each row in survey_ResponseDetail I will edit my answer with the code to apply the constraint you need to make this work.

Upvotes: 1

Joyce Babu
Joyce Babu

Reputation: 20654

For ON DUPLICATE KEY UPDATE to work, one of the updated / newly inserted fields must be a primary key or should have a unique index on it.

It need not be a single field. Composite keys can also be defined as unique.

Upvotes: 0

Related Questions