cdherold has asked for the wisdom of the Perl Monks concerning the following question:

monks,

I'm attempting to do a MySQL UPDATE using PERL.

I've got the INSERT working alright ... 

$sth = $dbh->prepare("INSERT INTO snp_db (rs,chr_position,band,alleles
+,het,validated,gene,role,relative_pos,aa_change,flanking_seq) VALUES 
+(?,?,?,?,?,?,?,?,?,?);");

$sth->execute($rs_number,$position,$band,$alleles,$het,$val,$gene,$rol
+e,$pos,$aa);

[download]

but my UPDATE doesn't seem to work ... 

$sth = $dbh->prepare("UPDATE snp_db SET (chr_position,band,alleles,het
+,validated,gene,role,relative_pos,aa_change,flanking_seq) VALUES (?,?
+,?,?,?,?,?,?,?) WHERE rs = $rs_number;");

$sth->execute($position,$band,$alleles,$het,$val,$gene,$role,$pos,$aa)
+;

[download]

Any ideas?

thanks monks,
chris

Replies are listed 'Best First'.
Re: MySQL UPDATE in PERL
by Cabrion (Friar) on Feb 25, 2003 at 01:47 UTC
    update table set attr=value, attr2=value, ...

    only insert follows the form (attr,attr2,...) values (val, val, ...)

    Get a good SQL reference.

[reply]
Re: MySQL UPDATE in PERL
by pfaut (Priest) on Feb 25, 2003 at 01:49 UTC

    The statement handle execute method returns status. It returns a value that evaluates as false to indicate failure. When this happens, you can get the error message by looking at the statement handle's errstr property. An alternative would be to set the RaiseError or PrintError option when you establish your database connection.

    If you had done one of these, DBI probably would have told you that the number of values supplied did not match the number of fields specified in the update statement. Unless my eyes are failing me, there are 10 fields listed but only 9 question marks in the value list. 

    ---
print map { my ($m)=1<<hex($_)&11?' ':'';
$m.=substr('AHJPacehklnorstu',hex($_),1) }
split //,'2fde0abe76c36c914586c';

    [download]
[reply]
[d/l]
Re: MySQL UPDATE in PERL
by tadman (Prior) on Feb 25, 2003 at 01:59 UTC
    You could also use the MySQL REPLACE INTO command which is actually quite useful for stuff like this. Of course, the syntax is different from INSERT INTO which might be the source of your confusion.
[reply]
Re: MySQL UPDATE in PERL
by Hagbone (Monk) on Feb 25, 2003 at 02:02 UTC
    Also wondering if you got an unintended semicolon in there ??? 
    <snippet>
WHERE rs = $rs_number;");
                    ^^^
</snippet>

    [download]
    You got any error message checking going on?
[reply]
[d/l]
      That's quite legal. Most SQL implementations use the semi-colon to delimit multiple SQL statments on a single line. The following should work just fine on most SQL implementations. 

      $dbh->do('update bar set attr=val;delete from baz;');

      [download]

      They are also required when using most native user interfaces as those don't have a line-continuation character. Consider: 

      SELECT *
FROM tab1, tab2
WHERE tab1.x = tab2.y
ORDER BY tab1.x desc; <-- this tells the interface you're ready to pro
+cess

      [download]
      That's alot nicer to read than one long sting. However, DBI's prepare adds the semi-colon if you don't.

      Then you have those crazy Sybase folks that want you to type 'go' instead of a semi-colon. . . sheeesh! At least Microsoft did one thing right when they assimilated that code!

[reply]
[d/l]
[select]
        Err - AFAIK the semicolon is NOT legal when sending a request via DBI. The semicolon (or "go", for Sybase isql users) is used in the interactive query tool to tell it that you're ready to send the SQL query that you just typed.

        Michael

[reply]
Re: MySQL UPDATE in PERL
by jaco (Pilgrim) on Feb 25, 2003 at 02:28 UTC
    then again you could do it the poormans way 
    
$update = $dbh->prepare("UPDATE snp_db SET chr_position=?, band=?, all
+eles=?, etc=? WHERE rs=$whatever");

$update->execute($val, $val2, $val3, $etcval);

    [download]

    though the above solution of using replace is probably better.
[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom