Jump to content

ON DUPLICATE KEY UPDATE - Positional Placeholders


Go to solution Solved by benanamen,

Recommended Posts

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 post
Share on other sites
  • Solution

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

  • Like 1
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.