Re: DBI and JOINs

Not sure if I follow your question exactly, but an alternative to your "foreach ... do-a-select", is to use the foreach to build a (possibly long) IN clause. Then one sql statement will do it:

my @values = ...; # get the values however
my $in_parts = join ',', @values;
my $sql = "select * from aTable where some_field in ($in_parts)";
[download]

You will have to deal with quotes if your field is not numeric.

There will be a little more Perl coding to sort out the results, but a lot better overall performance.

Comment on Re: DBI and JOINs Download Code

Replies are listed 'Best First'.
Re^2: DBI and JOINs by OfficeLinebacker (Chaplain) on Sep 30, 2006 at 03:45 UTC
I do exactly that in one of my programs but for an INSERT statement. a la `My @values # get updated/pushed on whatever--sometimes an arrayref fro +m a selectall_arrayref on another DB of a different subtype $dbh->do("insert into table1 (col2, col3, col4) values('",join("','",@ +values),"')");` [download] Well, something like that. I think by now I've refactored it to set a variable with the join and just interpolate that into the quoted SQL command just as in the post above. _________________________________________________________________________________ I like computer programming because it's like Legos for the mind.	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: DBI and JOINs
by OfficeLinebacker (Chaplain) on Sep 30, 2006 at 03:45 UTC

My @values # get updated/pushed on whatever--sometimes an arrayref fro
+m a selectall_arrayref on another DB of a different subtype
$dbh->do("insert into table1 (col2, col3, col4) values('",join("','",@
+values),"')");
[download]

_________________________________________________________________________________

I like computer programming because it's like Legos for the mind.

[reply]
[d/l]