Seriously, I'd use Class::DBI.

foreach my $row (MyCDBI::Class->retrieve_all) {
    if ($row->column1 =~ m/AR/i ) {
        $row->column2( 'AR' );
    }

    $row->update;
}
[download]

Do you mean I will need to write a sub-function to do the update and then call it after I set the value?
Bobby

inline or another sub is a matter of preference. My code example was how easy it would be to do what you're trying to do if you were using Class::DBI instead of directly using DBI.

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

And unrelated to your DBI question, but you might could use a data structure of regex objects rather than that frelling huge if monstrosity.

use Tie::RefHash;

tie my %m, "Tie::RefHash";

%m = ( qr/abc/ => "ABC", 
       qr/def/ => "XYZ", );

$str = "def";

for my $r ( keys %m ) { if( $str =~ $r ) { $str = $m{$r}; last; } }

print $str, "\n"
[download]

I'd agree about using a structure and walking it, rather than using that giant if/elsif/statement, but I wouldn't recommend using a hash in this case, without knowing more about the problem.

The example given doesn't specify what is in $row[4], and the regexes given don't lock the match to the full width of the string. Now, it's possible that the original poster intended to match something more like:

m/^ENG$/i;
m/^AVN$/i;
m/^TRAN$/i
...etc
[download]

But with what's given, the item being matched might contain 'QMI' (matches 'QM' and 'MI') or 'POLICE TRAN' (again, matches more than one), and so the order that the searches are done might be significant.

If the items being matched are the full width of the string, then the order isn't significant, but I'd probably not use regexes:

my %matches = (
  ENG      => 'EN',
  AVN      => 'AV',
  TRAN     => 'TN',
  QM       => 'QM',
  MI       => 'MI',
  POLICE   => 'MP',
  MP       => 'MP',
  CHAPLAIN => 'CH',
  INF      => 'IN',
  FA       => 'FA',
  MED      => 'MD',
  SIG      => 'SC',
  MAIN     => 'SS',
  AR       => 'AR',
);
if ( exists ( $match{uc($row[4])} ) {
  $row[3] = $match{uc($row[4])};
} else {
  $row[3] = 'PU';
}
[download]

Fletch,


Thanks for the note!


Side note, what do you mean "We're looking for people in ATL? Who is looking?


Thanks,
Bobby

We have an opening where I work; it was posted on jobs.perl.org but it's fallen out of the DB. I'm in the process of getting the details moved somewhere else that I can link to again and haven't re-linked my signature.

Update: And it's linked.

Updated again: And a more permanent official type link.

--
We're looking for people in ATL

UPDATE STAGING.STAGING_PREPOUICS
SET column3 = 'EN'
WHERE column4 = 'ENG';

etc...
[download]

Shiza
I have already gone down that road. The problem with that is that I would have to do substr(column4,0,?) to get the match. When the ENG doesn't appear in at the same spot each time, it would actually double the effort and time, hense the reason for the perl script. All this will be rolled into a automated process.
Thanks
Bobby

How about:

UPDATE STAGING.STAGING_PREPOUICS
SET column3 = 'EN'
WHERE column4 LIKE '%ENG%';
[download]

Like I said, I'm not familiar with Sybase but I know this would work with MySQL.

UPDATE foo
   SET col3 = 'EN'
 WHERE LOWER( col4 ) LIKE '%ENG%'
[download]

(or LCASE or LC or whatever, depending on the function for your specific database)

lots of options ... personally, I like the Class::DBI one. This is kinda what Class::DBI was created for.

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

You have a lot of options above for handling this in code, but personally I would suggest you handle it directly in the database.

I don't know what columns $row[3] and $row[4] represent, but your code is doing a mapping between the two, so create a table to hold the pairs. Something like:

create table curtisb_map
( row3_code   varchar(20)          -- pure guess
, row4_code   varchar(20)          -- purer guess
, primary key (row3_code, row4_code)
);
[download]

select s.*, m.row_3_code
from staging.staging_prepouics s
    inner join curtisb_map m on m.row3_code = s.whatever
[download]

The advantage with this approach is that the data is in the database where it belongs, instead of half in the database and half in the code. The next maintenance programmer that comes along will thank you profusely when she finds the mapping right alongside the data. And the bonus is that this is likely to run much faster.

As an aside, your code has something that makes me cringe every time I see it: select * is bad enough, but it's made worse by the assumption of column order (i.e., you're hoping that $row[3] and $row[4] are what you think they are). Do yourself a favor, call out the column names explicitly, and bind the results to meaningful variables. Sooner or later, this practice will bite you.