I think this is the sql u r looking for
update Tmaster hd set hd.PartCount = (select count(dt.detail_id) from
Tdetail dt where dt.Part_Id = hd.Part_Id group by dt.Part_Id)
Hope it helps
WangJun wrote:
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.
