A Small Perl Program involving mysql I need help with.

[tollbooth]

First, I'm not a PERL programmer, however I am in a quandry and need help. I am using MySQl and it does not support subselects. I have 2 databases - let's call A and B. Within database A, I have a field, x. Within database B, I have fields x, y, and z.

What I need to do is take field x in table B and see if it exists in table A, field x. If the fields are equal (a match is found), I need to update table B, field z with "yes". If a match is not found, I need to update field z in Table B with "no".

Any takers? This is very important to me and I would be eternally grateful!

Thanks in advance.

 

[sampsonr]

Can you clarify a little?

You refer to A and B as both "databases" and "tables". I assume you mean they're both tables in the same database.

When you say "...take field x in table B and see if it exists in table A...", it's not clear what the goal is. This sentence can be interpreted several ways. Can you describe what it is you actually need to do without the abstractions?

 

[tollbooth]

Sorry for the confusion, I meant table A and table B in the same database. I'm trying to do the following which is not supported in MySQL:

update tableB, tableA
set
tableB.z = 'y'
where
tableB.x = tableA.x;

 

[hmerrill]

Are you using the DBI and DBD::mysql modules in Perl?

Assuming you have DBI and DBD::mysql installed, you could create a script do this:

1. connect to mysql database
2. select all rows in table B
3. for each row in table B
save value of column x in variable $x
SELECT x
FROM table a
WHERE x = $x

*** You should use placeholders - read the perldocs on placeholders by doing

perldoc DBI

after the Perl DBI module is installed. Then search for "placeholder".

4. if you select from table a *DOES* find a record, you can then update table b column z.

It's a multi-step process, but not that tough to implement. But you are going to need to program in Perl, and if you don't already have them installed, you'll need DBI and DBD::mysql. Both DBI and DBD::mysql have nice perldocs to show you how to use them - the DBD::mysql perldoc shows you how to connect to the database. Read the DBD::mysql perldocs by doing

perldoc DBD::mysql

at a command prompt.

HTH. Hardy Merrill