skip empty mysql table

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

Hello!! I have a question, I am working on a big table that I need to turn into little tables using customernumber a lot of times the customernumber has no movements, the table appears with zero rows, so I want to tell the SELECT I use to not create the table if its zero rows, this is how I have it right now

CREATE TABLE $tableNameexpt SELECT * FROM final_exp WHERE pat = $row[0
+]
[download]

the last part is like that because I am programming in perl, when run it creates these table with zero rows, how can I avoid them?

my $sthexpt = $dbhexpt->prepare("SELECT DISTINCT PAT FROM xpatentes");
  $sthexpt->execute();
  # Just print the values of 'pat' from the patentes table.
  while ( (my @row) = $sthexpt->fetchrow_array() ) {
    print "El nombre del proximo archivo ... E$row[0]\n";
  }
 $sthexpt->execute();  # we've looped through all of the query results
+ above so

  #--
  # Create the new tables.
  #
  while ( (my @row) = $sthexpt->fetchrow_array() ) {
    my $tableNameexpt =  "E" . $row[0];  # table names can not begin w
+ith a digit

    print "Creando tabla " . $tableNameexpt . "\n";

    my $a_sthexpt = $dbhexpt->prepare("CREATE TABLE  TableNameexpt SEL
+ECT * FROM final_exp WHERE pat = $row[0]");

    $a_sthexpt->execute();

  }
[download]

I just wants to see create it the one with one or more rows, can someone help me?

Comment on skip empty mysql table Select or Download Code

Replies are listed 'Best First'.
Re: skip empty mysql table by pc88mxer (Vicar) on Feb 06, 2008 at 18:00 UTC
AFAIK, there isn't a way to conditionally create a table only if the SELECT returns some rows. You just have to query the number of rows in the new table and delete it if it is empty. That said, I'm not sure what you are gaining in your example by creating smaller tables. If you have an index on the `pat` column, then it is not that less efficient to just query the main table and add a 'WHERE pat = ...' clause to restrict your query to what would be in the smaller table. Also, if you create the smaller tables, you'll have to update them whenever you update, insert or delete rows from the main table. Finally, use placeholders! `my $a_sthexpt = $dbhexpt->prepare("CREATE TABLE TableNameexpt SEL +ECT * FROM final_exp WHERE pat = ?"); $a_sthexpt->execute($row[0]);` [download]	[reply] [d/l] [select]
Re: skip empty mysql table by dwm042 (Priest) on Feb 06, 2008 at 18:47 UTC
You can check for zero rows with: `SELECT count(*) from TABLE` [download]	[reply] [d/l]