In PHP, what are the advantages of using the ON DUPLICATE KEY UPDATE clause in an INSERT statement for handling duplicate records in a database table?

When inserting records into a database table, it is common to encounter duplicate key errors if the record being inserted already exists in the table. One way to handle this issue is to use the ON DUPLICATE KEY UPDATE clause in the INSERT statement. This clause allows you to update the existing record instead of throwing an error when a duplicate key is encountered. 

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Insert or update record using ON DUPLICATE KEY UPDATE
$sql = "INSERT INTO myTable (id, name) VALUES (1, 'John') 
        ON DUPLICATE KEY UPDATE name = 'John'";
        
if ($conn->query($sql) === TRUE) {
  echo "Record inserted/updated successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Keywords

PHP ON DUPLICATE KEY UPDATE INSERT statement duplicate records database table

Related Questions