benanamen Posted January 18, 2018 Share Posted January 18, 2018 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? Link to comment Share on other sites More sharing options...
benanamen Posted January 18, 2018 Author Share Posted January 18, 2018 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.