in reply to change data in the mysql through DBI

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]

Replies are listed 'Best First'.
Re^2: change data in the mysql through DBI
by baxy77bax (Deacon) on Apr 05, 2008 at 20:07 UTC
    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]

In Section Seekers of Perl Wisdom