Re: MySQL syntax error for ON DUPLICATE KEY UPDATE

[dvergin]

It seems that your SQL statement is failing because the placeholders in your "ON DUPLICATE KEY..." clause lack bind values when you invoke your SQL with "execute".

I suspect you are passing only five values when you "execute". Your statement requires ten values, i.e. TWO copies of your variable set -- one after the other.

HTH

[bradcathey]

That was it! Made perfect sense. Thanks!

my $stmt = 'INSERT INTO contact_form (' . join(',', keys %sql) . ') 
            VALUES (' . join(',', ('?') x keys %sql) . ') 
            ON DUPLICATE KEY UPDATE '. join(' = ?, ', keys %sql) . ' =
+ ?';
       
$self->dbh->do( $stmt, undef, (values %sql, values %sql));

[rhesa]

Alternatively, you can refer to the values in the insert part with the values() function:

insert into  foo    (bar, baz) 
             values (?,   ?)
on duplicate key update
             bar = values(bar)
           , baz = values(baz)

So your statement string would be:

my $stmt = 'INSERT INTO contact_form (' . join(',', keys %sql) . ') 
            VALUES (' . join(',', ('?') x keys %sql) . ') 
            ON DUPLICATE KEY UPDATE '. join(', ', map { "$_ = values($
+_)" } keys %sql);