Can I update a record if it exists, and insert it if not, for multiple rows in a single query?

Question

I am trying to write a query to insert an array of many values (say 1000) in a effective way using 'implode'. Here is the code.

$sql = array(); 
foreach( $return as $key=>$row ) {
$sql[] = '("'.mysql_real_escape_string($row['fullname']).'",   '.$row['age'].','.$row['since'].','.'"'.mysql_real_escape_string($row['description']).'")';
}
mysql_query('INSERT INTO data (name, age, since, description) VALUES '.implode(',', $sql));

My query inserts the record array into the data table. I want to change the query so that it will update the record if it exists, and otherwise it will insert the record.

duplicate: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql — Prasanth, Aug 27 '12 at 05:43
There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) to ensure the functionality of your project in the future. — TRiG, Dec 12 '14 at 12:44

Robbie · Accepted Answer · 2012-08-27T06:44:24.020

What you probably want is a version of the "ON DUPLICATE KEY" that actually allows you to reference the data being input.

INSERT INTO data (name, age, since, description) VALUES
     ("Bob", 23, "01-01-1980", "friend"),
     ("Bill", 33, "03-01-1980", "tall"),
     ("Jane", 43, "12-01-1980", "thin")
 ON DUPLICATE KEY UPDATE age=VALUES(age),
                         since=VALUES(since),
                         description=VALUES(description);

Hopefully self-explanatory when you read it?

$sql = array();   
foreach( $return as $key=>$row ) {  
    $sql[] = '("'.mysql_real_escape_string($row['fullname']).'",   '.$row['age'].','.$row['since'].','.'"'.mysql_real_escape_string($row['description']).'")';  
}  
mysql_query('INSERT INTO data (name, age, since, description) VALUES '.implode(',', $sql). ' ON DUPLICATE KEY UPDATE age=VALUES(age), since=VALUES(since), description=VALUES(description);');

Edit, adding this into the user's code above.

can you please modify my code such that it ll use implode for insert and update things i want if exist?? @Robbie — user1518659, Aug 27 '12 at 06:34
cool .. thanks..sry for disturbing much..i never thought to use DUPLICATE KEY on implode.. @Robbie — user1518659, Aug 27 '12 at 06:56

Can I update a record if it exists, and insert it if not, for multiple rows in a single query?

1 Answers1