with insert on duplicate key update - updating multiple rows at once

Question

With latest joomla version I try to achieve one query to the database and updating multiple rows at once.

I want to make a query like this:

INSERT INTO `tablename`
(`id`,`date`) VALUES 
('79','2018-12-01'),('78','2018-10-01'),('76','2018-06-01'),('80','2019-01-01')
ON DUPLICATE KEY UPDATE
(`date` = '2018-12-01'),(`date` = '2018-10-01'),(`date` = '2018-06-01'),(`date` = '2019-01-01')

If I setup this and debug:

$query
        ->insert($db->quoteName('#__tablename'))
        ->columns($db->quoteName($columns))
        ->values($values);

this only produces the first 3 row of my wanted query. How can I extend to add the text "ON DUPLICATE KEY UPDATE" and another array values to the end? Or there is another way of doing this? Its possible to add multiple query to setQuery, or concatenate queries?

Answer 1

Building a REPLACE INTO query is just as simple as building an INSERT ... ON DUPLICATE KEY UPDATE. Note that there is a difference between REPLACE INTO AND ON DUPLICATE KEY UPDATE.

Code:

$columns = ['id', 'date'];
$data = [
    [79, '2018-12-01'],
    [78, '2018-10-01'],
    [76, '2018-06-01'],
    [80, '2019-01-01']
];

$db = JFactory::getDbo();
try {
    foreach ($data as &$row) {
        $row = (int)$row[0] . ", " . $db->q($row[1]); // flatten 2-dim array and apply security techniques
    }
    $query = $db->getQuery(true)
                ->insert($db->qn('#__tablename'))
                ->columns($db->qn($columns))
                ->values($data);
    $db->setQuery(substr_replace($query, 'REPLACE', 0, 8));    // swap INSERT for REPLACE
    // in other words: $db->setQuery(str_replace("\r\nINSERT", "REPLACE", $query));
    // or:             $db->setQuery(preg_replace("~^\s+INSERT~", "REPLACE", $query));
    // Joomla puts \r\n at the start of the query; see via var_dump($db) after setQuery()
    /*
    echo "<pre>";
        print_r($db);   // see the updated sql object
    echo "</pre>";
    */
    $db->execute();
} catch (Exception $e) {
    echo  "Syntax Error: " , $e->getMessage();  // never show php's error message to the public
}
echo "<div>" , $db->getAffectedRows() , " row(s) inserted</div>";

This is the query built:

REPLACE INTO `#__tablename`
(`id`,`date`) VALUES 
(79, '2018-12-01'),(78, '2018-10-01'),(76, '2018-06-01'),(80, '2019-01-01')

This will ALWAYS print the same number of affected rows for your batch of four entries:

4 row(s) inserted

---

---

As for ON DUPLICATE KEY UPDATE..., this is as simple as appending the clause to the end of the Joomla-generated query and including VALUES() syntax. (only replace the $db->setQuery() line from the above snippet)

$db->setQuery($query . " ON DUPLICATE KEY UPDATE `date` = VALUES(`date`)");

to create this query:

INSERT INTO `#__tablename`
(`id`,`date`) VALUES 
(79, '2018-12-01'),(78, '2018-10-01'),(76, '2018-06-01'),(80, '2019-01-01')
ON DUPLICATE KEY UPDATE `date` = VALUES(`date`)

This type of query behaves differently and gives different feedback in terms of affected rows.

When identical rows exist in the database table for all of the rows that you intend to add, you will see 0 affected rows. If the PRIMARY ID already exists -- let's say 1 row has a different date value than what is in the query -- you will see 2 affected rows (I have run this on my own localhost and despite other things I've read, this must be removing, then adding). So this kind of information can be either very helpful or very confusing depending on what you are trying to determine. Imagine if you have all four ids represented in your table already and two of the four rows need the date value to be updated. After the query executes, you will be notified that 4 rows have been affected.

Here is a relevant answer that I posted a few years ago on StackOverflow.