multiple sql question

[WangJun]

Hi all,

 

I am a beginner in JDBC sql. I have two table Tmaster(Part_ID, PartName, PartCount) and Tdetail(detail_id, Part_ID, PartUsageInformation). One Tmaster record(Part_ID) corresponds multiple Tdetail records, I want to compute the records number of every Part_Id in Tdetail table every month, then add it to the PartCount in table TMaster. I can do it in three steps:

 

1. select the Part_ID in the first table to x in a loop:
 String sql = "Select Part_Id from Tmaster"
 ...
 while (rs.next()) {x=...}

 

2. Then within the loop, compute the records number with the condition "Part_ID=x" in the second table to y

 

3. update the first table:
 String sql = "Update Tmaster set PartCount = " + y ;
 stmt.executeUpdate(sql);

 

I wonder whether a better solution exists, and I alse wonder whether step 3 is conflicted with step 1, for step 2 and step 3 is whithin the loop of step 1. Any mail is appreciated.

 

Thanks in advance.