Re: MySQL syntax error for ON DUPLICATE KEY UPDATE

[dvergin]

in reply to MySQL syntax error for ON DUPLICATE KEY UPDATE

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);