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

Hi, i need help with iteration over the database. first i would like to set up an array of things for which I would like to search my database for, than i would like to change every data that i found in the database with a data defined in the second array example: 
 use strict;
use DBI;


my $driver = "mysql";
my $dns = "database=baxy";
my $username = "baxy";
my $password  = "";

my $dbh = DBI -> connect ("dbi:$driver:$dns", $username, $password, {A
+utoCommit => 1});

my $titula = [qw(ing dipl. dipl.ing. dr phd)];
my $wage = [qw(10 20 30 50 200)];

my $sth = $dbh->prepare ("update Staff set wage = @$wage where positio
+n = ?") or die "$DBI::errstr";
foreach my $x (@$titula) {
$sth -> execute($x)|| die "$DBI::errstr";
}
$sth -> dump_results();

[download]
this obviously doesn't work. but the thing is that each data in the @titula that was found in the database i would like to change and associate with data in the @wage any advice would be helpful. thank you, robert

Replies are listed 'Best First'.
Re: change data in the mysql through DBI
by Corion (Patriarch) on Apr 05, 2008 at 18:10 UTC

    It's not clear to me what you want to do. Maybe you can tell us your table structure. Are you sure that the following SQL statement doesn't do what you want? You can roll these 5 into one statement if you have a very large table (I guess starting at a billion rows or so) that you want to do one scan over, but doing the 5 statements might be more suitable.

    UPDATE staff
SET wage = ?
WHERE position = ?

    [download]
[reply]
[d/l]
      ok, this is a simplified structure of what i want to do. in one file (*) o have undetermined number of, let say words. in the other file (+) i have also undetermined number of words. also i have a table in the database which has two columns. i wish to search one column of the table for any word stated in the file (*) and than if there is a matched string a want to replace it with a string of the same index(like index in the array - that is why i think a good solution would be turning those files(* ,+) into arrays) in the file (+). 
      
(*)file                    (+)file
_______                    _______
1a1                            sss
2b2                            ggg
3c3                            uuu
4d4                            bbb
5e5                            iii
6e6                            zzz
...                            ...

table
____________
3j4    |    rrr
2b2    |    ttt
3w3    |    jjj
5e5    |    ppp
1s1    |    kkk
4d4    |    aaa
...


the result should look like :

table
__________
3j4    |    rrr
2b2    |    ggg
3w3    |    jjj
5e5    |    iii
1s1    |    kkk
4d4    |    bbb
...

      [download]
      also never mind about the length of the data let say the two files have the same amount of data but i don't know what the number is and i don't care. P.S. thanks for the reply
[reply]
[d/l]

        The easy (and a bit slower) way is to do a loop of update statements:

        use strict;
use DBI;

my @left_column = do { open my $fh, "<", 'data/left_file.txt' };
my @right_column = do { open my $fh, "<", 'data/right_file.txt' };

chomp @left_column;
chomp @right_column;


my $dbh = DBI->connect(...);
my $sth = $dbh->prepare(<<SQL);
UPDATE wordtable SET right_column = ?
WHERE left_column = ?
SQL;

for my $index (0..$#left_column) {
    $sth->execute($right_column, $left_column);
};

        [download]

        You will want to read about the placeholders in the DBI documentation.

        A different approach might be to construct one huge CASE statement, but you might run against the query text size limits of your database:

        my $statement = 'UPDATE wordtable SET right_column = CASE ';
$statement .= join "\n", map { "WHEN left_column = '$left_column[_]' T
+HEN '$right_column[$_]'" } 0..$#left_column;
$statement .= "END;";
print $statement;

        [download]
[reply]
[d/l]
[select]
Re: change data in the mysql through DBI
by bradcathey (Prior) on Apr 06, 2008 at 02:10 UTC

    Though I've never used one, if you are using MySQL ver 5.01+ you might take a look at setting up a batch file. If the table changes often, you should consider a establishing a View.

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

Back to Seekers of Perl Wisdom