Hi,

I would guess that it's becuase you have no spaces before the WHERE clause.

But why do it with multiple commands as you are updaing the same database, this would do the same:

$SQL = ("UPDATE customers SET name = '$firstName $lastName', email = '$email', phone = '$phone', city = '$city', state = '$state' WHERE custID = '$custID';"); 
$DB->do($SQL) ¦¦ die $DBI::errstr; 
print $SQL; 

Does the same your 5 commands in one command, and so will be much quicker and less resource hungry.

But if there were no space, it would error.

I misread the question - no you are better off combining the params in one select as said above, but you can build a dynamic list and loop through it:

%vars = ('name',"$firstName $lastName", 'email', "$email", 'phone',"$phone",'city',"$city",'state',"$state");

$SQL = UPDATE customers SET ";
foreach $v (keys %vars) {
$SQL .= qq¦$v='$vars{$v}',¦;
}
$SQL =~ s/,$//; # Last comma
$SQL .= qq¦ WHERE custID = '$custID';¦;

Tell me, are you calling this from the web? I found it more useful to do this:

$DB->do($SQL) ¦¦ &error("Cannot update: $SQL");

sub error {
my ($err);
$err = shift(@_);
print "content-type: text/html\n\n";
print Error: $err";
exit 0;
}

This stops the program immediately and displays the offending select.

Usually when I have these "won't update" deals I find it's that $custID is NOT populated as I think it is.

$SQL="UPDATE customers SET name='$firstName $lastName' WHERE custID = '$custID'";
$debug .= "$SQL <br>\n";

print "content-type: text/html\n\n";
print "Update Successful";
print "debug: $debug";

Might yield

UPDATE customers SET name='Michael BlueJay' WHERE custID = ''

Whew, lots of good help, and lots to respond to.

MikeyB, I used tabs in my queries to line them up and the tabs didn't make it through when I pasted into this forum. But I just tried it with spaces instead of tabs in my code and I had the same problem.

I didn't know about that syntax to separate the updates with commas. I tried it and it works great, thanks. This is what I need.

Rocknbil, thanks for your looping alternative, I'm filing that away too.

I'm not sure what you mean, "Calling it from the web." I was typing the filename directly into a web browser, if that's what you mean.

$custID was definitely populated, I set it manually in my test. And like I said, when I copied and pasted the $SQL generated by the Perl program into phpMyAdmin, it worked fine.

But even though I can now do similar multiple SQL UPDATEs in Perl via two different methods, I'm still wondering: Is there any way to send a true multiline MySQL command block with Perl? Such as:

INSERT into table (field1, field2) VALUES (value1, value2)
UPDATE table SET field=value WHERE condition

You've gotten good advice from the above.

Doing a bunch of sequential statements like that is horrible programming practice. Use ONE statement to update ALL the fields for one customer id.

Neither DBI or MySQL is not setup to handle multiple statements the way you are trying to do it.

Secondly, PHPMYAdmin does not behave the same as the DBI... it parses and interprets the way it THINKS you want to do it.

Lastly, you should $DB->quote($each-variable) prior to using them in any sql that has it's input sourced from the outside world as in a browser. A lot safer operating practice.

I've been manually escaping my variables before inserting. I couldn't find any documentation for $DB->quote(). Are you sure that's the right syntax?

I agreed to update multiple fields with one command, that's fine with me. But I'm still interested in feeding multiple commands to MySQL at the same time, such as:

INSERT into table (field1, field2) VALUES (value1, value2)
UPDATE table SET field=value WHERE condition