Compassionate (3/2/2013)

---

Hi All,

I have two tables Tab1 and Tab2.For each row in Tab1, the table Tab2 contains multiple rows.Important columns in both tables are C0,C1 and C2. So i have to update the C1 and C2 columns in Tab1 with count(C1) and max(C2) for each C0.
assume C0="Temp" and Tab2 has 10 rows for Temp.So Tab1 should be updated with C1 as 10 and C2 asmax(C2) in Tab2.

so i wrote a cursor as below.It takes 1 min for two rows in Tab1.Any better solution ?

declare T1_cursor CURSOR for
select distinct C0 from Tab1

OPEN T1_cursor

FETCH NEXT FROM T1_cursor
INTO @C0

WHILE @@FETCH_STATUS = 0
BEGIN
select @recordCount=count(*)+1,@maxTime=max(maxtime) from Tab2
where C0=@C0

update T1 set RecordCount=@recordCount , Maxtime=@maxTime where C0=@C0

FETCH NEXT FROM T1_cursor
INTO @C0

END -- End of cursor

CLOSE T1_cursor
DEALLOCATE T1_cursor

 UPDATE t1
    SET RecordCount = preagg.RecordCount,
        MaxTime     = preagg.MaxTime
   FROM dbo.Tab1 t1
   JOIN (
         SELECT CO, RecordCount = COUNT(*)+1, MaxTime = MAX(MaxTime)
           FROM dbo.Tab2
          GROUP BY CO
        ) preagg
     ON preagg.CO = t1.CO
;