Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: MySQL syntax error for ON DUPLICATE KEY UPDATE

by dvergin (Monsignor)
on Jul 20, 2008 at 01:48 UTC ( #698870=note: print w/ replies, xml ) Need Help??


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


Comment on Re: MySQL syntax error for ON DUPLICATE KEY UPDATE
Re^2: MySQL syntax error for ON DUPLICATE KEY UPDATE
by bradcathey (Prior) on Jul 20, 2008 at 03:00 UTC

    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));
    —Brad
    "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
      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);

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://698870]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (8)
As of 2014-12-27 02:12 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (176 votes), past polls