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

Question

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`)

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, Apr 21 '14 at 06:44
@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, Apr 21 '14 at 06:55
@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, Apr 21 '14 at 09:01
`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, Apr 22 '14 at 12:11
@Wrikken this problem solved . thank for your comment. its useful to others — Babu, Apr 22 '14 at 12:13

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

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.

edited May 23 '17 at 10:30

Community

1
1

answered Apr 21 '14 at 06:57

Alex

8,055
7
39
61

can you please give the example parameter for this function . $table, $columns, $duplicates how to pass these values – Babu Apr 21 '14 at 09:03
@RyanBabu sure, i added some example – Alex Apr 21 '14 at 09:06
before this any fields indexing needed? please review my question on edited section . that query does not worked . Where i did mistake i can't understand my problem here . your code is useful i know . i just run that sql alone . its not update the existing value . Thanks for your example. nice to implement – Babu Apr 21 '14 at 09:16
INSERT INTO marks(`student`,`param,mark`, `created`,`lastmodified`, `status`) there is a error: `param,mark` it must by 'param', 'mark' – Alex Apr 21 '14 at 09:19
I tried again alex but not get updated only 0rows affected . i updated my question – Babu Apr 21 '14 at 09:26
1

I find out my problem @alex thanks for your answer now i use your function . i am not update the other values .**`param`=values(`param`), `student`=values(`student`) ** This is no need here . both values are same . now its working – Babu Apr 21 '14 at 09:54

score -1 · Answer 2 · answered Apr 21 '14 at 07:31

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();
    }

did you read his question? he said that he want do that in one query — Alex, Apr 21 '14 at 09:07
thanks for your advice . my application does not need the DB change feature . i am also using this type of process. i want to change this into sql because of DB hit rate . The insertion rate is very high . This process look like slow for me .i need optimization. — Babu, Apr 21 '14 at 09:08

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

2 Answers2

Linked