All

I'm trying to get a particular field to update in my database. The code below reads from a table in my database. If the value of $row4 matches what I'm looking for change the values of $row3.

Now this works from the command prompt with no problem. What I'm trying to figure out is how do I update $row3 in my database table. Do I have to do a push or prepare another statement and then call it from there?

I'm just looking for some guidence on this one. Thanks in advance

Bobby

b.curtis@stanleyassociates.com

#!/usr/bin/perl 
#
#Connects to a Sybase Database

use DBI qw(neat_list);  #Loads DBI module

#Connect....
my $dbh = DBI->connect("dbi:ODBC:davs", "<username>", "<password>", {
        PrintError => 0,
        RaiseError => 1
        }) or die "Can't connect to database: $DBI::errstr\n";

#Prepare statement....
my $sth = $dbh->prepare("SELECT * FROM STAGING.STAGING_PREPOUICS;") or
+ die "Can't prepare SQL statement: $DBI::errstr\n";

#Excute statement....
$sth->execute or die "Can't execute SQL statment: $DBI::errstr\n";

#Retrive Rows....
while( @row = $sth->fetchrow_array() )
{
    if($row[4] =~ m/ENG/i)
    {
        $row[3]='EN';
    }
    elsif($row[4] =~ m/AVN/i)
    {
        $row[3]='AV';
    }
    elsif($row[4] =~ m/TRAN/i)
    {  
        $row[3]='TN';
    }
    elsif($row[4] =~ m/QM/i)
    {  
        $row[3]='QM';
    }
    elsif($row[4] =~ m/MI/i)
    {  
        $row[3]='MI';
    }
    elsif($row[4] =~ m/POLICE/i || $row[4] =~ m/MP/i)
    {  
        $row[3]='MP';
    }
    elsif($row[4] =~ m/CHAPLAIN/i)
    {  
        $row[3]='CH';
    }
    elsif($row[4] =~ m/INF/i)
    {  
        $row[3]='IN';
    }
    elsif($row[4] =~ m/FA/i)
    {  
        $row[3]='FA';
    }
    elsif($row[4] =~ m/MED/i)
    {  
        $row[3]='MD';
    }
    elsif($row[4] =~ m/SIG/i)
    {  
        $row[3]='SC';
    }
    elsif($row[4] =~ m/MAIN/i)
    {  
        $row[3]='SS';
    }
    elsif($row[4] =~ m/AR/i)
    {  
        $row[3]='AR';
    }
    else
    {
        $row[3]='PU';
    }
    print "Unit Info:\n";
    print "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5]\n";


}
warn "Data fetching terminated early by error:  $DBI::errstr\n"
    if $DBI::err;

#Finish statement....
$sth->finish;

#Disconnect.....
$dbh->disconnect or warn "Disconnection failed: $DBI::errstr\n";

exit;

[download]

In reply to Updating database question by curtisb

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.