- Joined
- Jul 8, 2005
- Messages
- 22,492
- Likes received
- 999
- Thanks received
- 191
- Location
- Castle Saburac
Update table2 t2 set nolines = (select count(*) from table1 t1 where t1.id=t2.id and t1.somecode=2)
It won't be too efficient, I haven't tested it and some versions of SQL aren't happy with it.
You could just re-create table2 to get all the counts; create table2 as select id, desc, count(*) nolines from table1 group by id, desc
Note; not sure this is going to work in the way you want, because you may not want to update all the rows in t2.

Fails in sql 2005
cannot use aggregate in update row
Indeedy, I have had to create a third table, which as you say, is Sh 1 te
![]()

do it in SSIS if you're using 2005 - and you get to add a new skill to your CV![]()
Update table2 t2 set nolines = (select count(*) from table1 t1 where t1.id=t2.id and t1.somecode=2)
It won't be too efficient, I haven't tested it and some versions of SQL aren't happy with it.
You could just re-create table2 to get all the counts; create table2 as select id, desc, count(*) nolines from table1 group by id, desc
Note; not sure this is going to work in the way you want, because you may not want to update all the rows in t2.