On Duplicate Key Update - Multiple Columns

On Duplicate Key Update - Multiple Columns

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

The Syntax and Logic Behind Multi-Column Updates

When working with INSERT ... ON DUPLICATE KEY UPDATE in MySQL, a common point of confusion arises regarding how to handle multiple columns in the update clause. It is easy to assume that because you list multiple columns in the initial VALUES statement, the corresponding UPDATE clause must follow a different structure or require aggregation.

The syntax, however, is straightforward and consistent: you simply list the column assignments separated by commas, exactly as if you were writing a standard UPDATE statement. If you are inserting into col1, col2, col3, and col4, but your unique constraint (primary key or unique index) is on col1, then the update clause should target the other columns:

INSERT INTO table1 (col1, col2, col3, col4) 
VALUES ('$val1', '$val2', '$val3', '$val4')
ON DUPLICATE KEY UPDATE 
    col2 = '$val2',
    col3 = '$val3',
    col4 = '$val4';

Notice the absence of a SET keyword. The UPDATE clause in this specific context is an extension of the INSERT syntax, not a standalone update operation. Consequently, you cannot use parentheses around the assignments (e.g., (col2 = ..., col3 = ...)), as standard SQL does not allow list aggregation in that manner within assignment expressions. The comma-separated list is the correct and only valid format for multiple targets.

Handling Expressions and Functions

One of the most powerful aspects of this syntax is the ability to use expressions or functions on the right-hand side of the assignments. Unlike a standard UPDATE statement where you might strictly read from existing columns, ON DUPLICATE KEY UPDATE allows you to reference values already present in the INSERT list or combine them with existing data.

Consider a scenario where you want to increment a counter only if a row exists, but also ensure that other fields are updated regardless of whether the insert succeeded. You can mix literal values and column references:

ON DUPLICATE KEY UPDATE 
    col2 = VALUES(col2), -- Uses value from the INSERT clause
    col3 = col3 + 1,     -- Standard increment logic
    col4 = NOW();        -- Function call to set a timestamp

Using VALUES(col_name) is particularly useful here because it disambiguates between the column name (which might appear in both the target and source lists) and the value being inserted. This prevents ambiguity errors when dealing with columns that share names across the insert and update sections.

Implementation Considerations in PHP

When implementing this logic within PHP, the syntax remains consistent whether using the older mysql_* functions or modern alternatives like PDO or MySQLi. However, the security implications are critical. The source material mentions using string interpolation directly into the query:

$result = mysql_query("INSERT INTO table1 (col1, col2, col3, col4) 
                       VALUES ('$val1', '$val2', '$val3', '$val4')
                       ON DUPLICATE KEY UPDATE col2='$val2', col3='$val3', col4='$val4'");

While the SQL syntax is correct, embedding variables directly into the query string without sanitization invites severe SQL injection vulnerabilities. The deprecated mysql_* extension should not be used in production environments due to this lack of built-in prepared statement support.

Modern approaches utilizing PDO or MySQLi allow you to separate the logic from the data safely:

$stmt = $pdo->prepare("INSERT INTO table1 (col1, col2, col3, col4) 
                       VALUES (:val1, :val2, :val3, :val4)
                       ON DUPLICATE KEY UPDATE 
                           col2 = :val2, 
                           col3 = :val3, 
                           col4 = :val4");

$stmt->execute([
    'val1' => $val1,
    'val2' => $val2,
    'val3' => $val3,
    'val4' => $val4
]);

This approach ensures that the database engine handles escaping and type coercion, maintaining data integrity while executing the complex ON DUPLICATE KEY UPDATE logic correctly. For developers working with Laravel, the Eloquent ORM provides a fluent interface to achieve similar results without writing raw SQL, though understanding the underlying syntax is essential for debugging or optimizing performance-critical paths.

Final Note on Constraints

Remember that this operation only triggers if a duplicate key violation occurs. If col1 (assuming it holds the unique index) does not exist in the table, the entire statement—both the insert and the update parts—is skipped. This atomic behavior ensures data consistency without requiring explicit transaction blocks in most read/write scenarios, though wrapping complex logic in transactions is still recommended for high-concurrency applications.

Our network