Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

MySQL syntax error for ON DUPLICATE KEY UPDATE

by bradcathey (Prior)
on Jul 19, 2008 at 23:35 UTC ( #698862=perlquestion: print w/ replies, xml ) Need Help??
bradcathey has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monasterians,

I'm new to the INSERT ... ON DUPLICATE KEY UPDATE concept and now have been staring at this for an hour and not seeing the problem. Here's the code:

my $stmt = 'INSERT INTO contact_form (' . join(',', keys %sql) . ') VA +LUES (' . join(',', ('?') x keys %sql) . ') ON DUPLICATE KEY UPDATE ' +. join(' = ?, ', keys %sql) . ' = ?';

Data::Dumper gives me:

$VAR1 = 'INSERT INTO contact_form (more_info,city,name,address,comment +s) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE more_info = ?, city = ? +, name = ?, address = ?, comments = ?';

Run-time error message

...for the right syntax to use near ' city = , name = , address = , co +mments ='

What am I missing? (BTW, it works fine if I leave off the

ON DUPLICATE KEY UPDATE ' +. join(' = ?, ', keys %sql) . ' = ?'
)

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Comment on MySQL syntax error for ON DUPLICATE KEY UPDATE
Select or Download Code
Re: MySQL syntax error for ON DUPLICATE KEY UPDATE
by pc88mxer (Vicar) on Jul 20, 2008 at 00:08 UTC
    I would examine what SQL is actually being sent to the server. Check your server logs or see the section on "TRACING" in the DBI documentation.
Re: MySQL syntax error for ON DUPLICATE KEY UPDATE
by dvergin (Monsignor) on Jul 20, 2008 at 01:48 UTC
    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

      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: perlquestion [id://698862]
Approved by pc88mxer
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (15)
As of 2014-07-10 21:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (216 votes), past polls