ON DUPLICATE KEY UPDATE - Positional Placeholders

[benanamen]

I am running a ON DUPLICATE KEY UPDATE query using positional place holders and I get an error "SQLSTATE[HY093]: Invalid parameter number"

 

The query run directly on mysql works no problem. Never run into this before.

 

The good query:

INSERT INTO temporary_mailing_address ( mailing_address_id, mailing_address) VALUES (1, 'Street') ON DUPLICATE KEY UPDATE mailing_address_id = 1, mailing_address = 'Road'

The bad PDO

<?php
$sql = "INSERT INTO temporary_mailing_address ( mailing_address_id, mailing_address) VALUES (?, ?)
    ON DUPLICATE KEY UPDATE mailing_address_id = ?, mailing_address = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute(array(
$_POST['mailing_address_id'],
$_POST['mailing_address']
));

What gives?

[benanamen]

SOLVED. After some searching and RTFM I found the answer and learned something new. I needed to use something called the "VALUES(col_name)".

 

Per the manual:

 

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

 

https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html