in reply to Updating database question

You could also approach this problem from a different angle. Instead of selecting everything from your table and running updates on every row you could just do the dirty work with SQL and let the DB handle the processing: 

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

etc...

[download]
This will minimize the number of queries that you will run. Combine this with what fletch suggested and you'll have some code that's easier on your eyes, easier to maintain, and easier on your server. :)

Note: I have never used Sybase so I'm not sure if it supports this.

Replies are listed 'Best First'.
Re^2: Updating database question
by curtisb (Monk) on Jun 30, 2005 at 18:59 UTC
    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
[reply]
      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.
[reply]
[d/l]
        Shiza
        Ok, I will admit, I spoke to soon. I guess I've just been looking at the problem for to long today. YOu were right. Thanks,
        Bobby
[reply]
      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?
[reply]
[d/l]
        I'll all for Class::DBI. Wouldn't the best solution be to add a handler to the class for that table: 
        __PACKAGE__->set_sql(query_name_here => qq{
    UPDATE __TABLE__
    SET col3 = ?
    WHERE LOWER( col4 ) LIKE CONCAT('%', ?, '%')
};

        [download]
        I'm not sure if the CONCAT function would be necessary.
[reply]
[d/l]

In Section Seekers of Perl Wisdom