Return to Answer

As for ON DUPLICATE KEY UPDATE, this is as simple as appending the clause the built query and including VALUES() syntax. (only change one line from the above snippet)

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

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.

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

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.

$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->quoteName('#__tablename'))
                ->columns($db->quoteName($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>";

$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>";

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

$db = JFactory::getDbo();
try {
    $query = $db->getQuery(true)
                ->insert($db->quoteName('#__tablename'))
                ->columns($db->quoteName($columns));
    foreach ($data as $row) {
        $query->values((int)$row[0] . ", " . $db->q($row[1]));
    }
    $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>";

$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->quoteName('#__tablename'))
                ->columns($db->quoteName($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>";