Second - as others have pointed out, this has nothing to do with perl. In addition, it's not clear which column in ICD_9_Disease_Types you wish to update from the data in the two other tables.

Michael

Does anyone kindly have any tips ?

Yes. Fire your database designer and buy a good book on data modelling.

Your normalization is completely wrong.

How do you know it's wrong? Normalization is not done in a vacuum, but rather in response to a problem. Anybody who normalizes all of his tables is a fool for not understanding when to apply that particular tool.

I'm not saying you're wrong, and in fact you may be correct (the odds do favor you :). It's just that you don't have enough information about the context of the problem to know that you are right. Besides Fire your database designer is hardly helpful.

PerlJam

How do you know it's wrong?

'Cause I am a wizard! Seriously, though, have a look at this snippet.

   All_Cancer int DEFAULT '0' NOT NULL,
   Lung_Cancer int DEFAULT '0' NOT NULL,
   Breast_Cancer int DEFAULT '0' NOT NULL,
   Cervical_Cancer int DEFAULT '0' NOT NULL,
   Colorectal_Cancer int DEFAULT '0' NOT NULL,
   Skin_Cancer int DEFAULT '0' NOT NULL,
[download]

Doesn't it scream "repeating columns" to you?

And what happens when somebody has some cancer not in this list? To me, these columns should be VALUES, while the column names should be "disease_code" and "disease_name". Typical symptom that the table is not even in first Normal Form.

Besides Fire your database designer is hardly helpful.

That's called a "sarcastic comment". Next time, I'll post a sign. ;-P

Let me explain in clearer English. I have a list of all the disease codes. I wish to go down this list and take each code one by one. I want to know what disease categories each element of this list falls into. The user sets the code ranges for the disease categories and this information is held within the second table. Each row of the second table represents a range. Therefore, in this second table, a single disease category may be represented on more than one row.

I need a lookup table so that all the ICD codes for a particular disease can be lookup with a simple statement and this is why I wish to create the third table.

I don’t know whether this can be done with a series of SQL statements or whether Perl DBI could be used. I was assuming that Perl or equivalent should be used. I see no reason why this question should not be posted here.

It's very hard to figure out what you are asking. This appears to be an SQL update query, but I am not entirely sure. Part of what you want looks to be:

UPDATE ICD_9_Disease_Types
SET    Circulatory_Disease = 1
WHERE  CHD in (
SELECT ICD_9
FROM   ICD_9,
       ICD_specifications_for_disease
WHERE  ICD_9.ICD_9 BETWEEN
       ICD_specifications_for_disease.ICD_start
AND    ICD_specifications_for_disease.ICD_end
       );
[download]

What does this have to do with perl? It's really a database question from what I can see.

I was looking for a Perl solution to the problem. eg. using DBI.

Your problem is still within the SQL domain, and no amount of DBI expertise can help you with your question. Once you have resolved your database questions and have run into DBI problems that the fine manuals don't answer, c'mon back and we'll do our best to help you out.