Re^2: change data in the mysql through DBI

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

Comment on Re^2: change data in the mysql through DBI Download Code

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

Replies are listed 'Best First'.

Re^3: change data in the mysql through DBI
by Corion (Patriarch) on Apr 05, 2008 at 20:21 UTC

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]