Use" INSERT ... ON DUPLICATE KEY UPDATE" to insert multiple records

5
votes

My Table structure

table: marks enter image description here

My objective: i want to insert or update multiple records with the condition

i am currently check by this query

1st step 

SELECT * FROM `marks` WHERE `student` =115 AND `param` =1

2nd step 

if 
    records found by matching above criteria i just update record by my new values 
else
    insert new record into my table

It gonna working fine . but i want to reduce code and optimize this into single query . its possible or not ? I found this on MySQL docs INSERT ... ON DUPLICATE KEY UPDATE . if this is a solution . how can i achieve by query ?

Note: i am using the Yii framework . suggestion from Yii also welcome

Edited: This query does't not update the rows . but escape from insert working correctly

INSERT INTO marks(`student`,`param,mark`,`created`,`lastmodified`,`status`) 
VALUES 
(11,30,10,'00-00-00 00:00:00','00-00-00 00:00:00','U')
ON DUPLICATE KEY UPDATE `mark`=VALUES(`mark`)
2
phpmysqlyiiinsert-update
First step: create a unique index on (student, param), then just try to insert like you would , ON DUPLICATE KEY UPDATE SET mark=VALUES(mark) – Wrikken
@Wrikken how to set the two column combination as a unique . because different students have same param's . both student and param combination only unique . i not to familiar with indexing . plese need more info . thanks – Babu
@Wrikken i edit my question . please review it . it does not update the existing record . but its skip the duplicate entry correctly. i add the unique index for the fields student and param . – Babu
ALTER TABLE marks ADD UNIQUE (student, param);, and then INSERT INTO marks (student, param, mark, created) VALUES (11,20,10,NOW()) ON DUPLICATE KEY UPDATE SET mark=VALUES(mark); should work. – Wrikken
@Wrikken this problem solved . thank for your comment. its useful to others – Babu

2 Answers

3
votes

Check this article Yii INSERT ... ON DUPLICATE UPDATE. They suggest you don't use this feature. But i want it to use, so I extended from CDbCommand my own component and add method for ON DUPLICATE KEY UPDATE:

public function insertDuplicate($table, $columns, $duplicates)
{
    $params=array();
    $names=array();
    $placeholders=array();
    foreach($columns as $name=>$value)
    {
        $names[]=$this->getConnection()->quoteColumnName($name);
        if($value instanceof CDbExpression)
        {
            $placeholders[] = $value->expression;
            foreach($value->params as $n => $v)
                $params[$n] = $v;
        }
        else
        {
            $placeholders[] = ':' . $name;
            $params[':' . $name] = $value;
        }
    }

    $d = array();
    foreach($duplicates as $duplicate)
    {
        $d[] = '`' . $duplicate . '` = VALUES(`'.$duplicate.'`)';
    }
    $sql='INSERT INTO ' . $this->getConnection()->quoteTableName($table)
        . ' (' . implode(', ',$names) . ') VALUES ('
        . implode(', ', $placeholders) . ') ON DUPLICATE KEY UPDATE ' . implode(', ', $d);
    return $this->setText($sql)->execute($params);
}

Usage example:

Yii::app()->db->createCommand()->insertDuplicate('user', [
   'id' => $this->id,
   'token' => $token,
   'updated' => date("Y-m-d H:i:s"),
], ['token', 'updated']);

This command will create user with this parameters or update token and updated fields if record exists.

-1
votes

Do not implement "INSERT ... ON DUPLICATE" in your programming as it is MySQL specific. When you migrate from MySQL to other Database your program cannot communicate with new Database as per the concept of Database Abstraction

try like this

    function actionName()
    {
        $id=$_POST['marks']['id']; //Pick the ID from your form values
        $model = Marks::model()->findByPk($id); //Check the records in the Marks table
        if (!$model) //If not found create a new Model for insertion. If records are there it will update the model
            $model = new Marks();
        $model->attributes = $_POST['marks']; //Assign the form values to attributes
        $model->save();
    }