SQL question

[EternalOptimist]

syntax quessie

i have a table ID,description,somecode (1,000 records)



I create a second table grouped on ID, descr,0 as noLines from table 1 (50 records)


I want to update table2.noLines withe the count from table1 where table1.somecode = 2, joined on id

 

[oraclesmith]

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.

 

[EternalOptimist]

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

 

[Pogle]

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

 

[EternalOptimist]

do it in SSIS if you're using 2005 - and you get to add a new skill to your CV

I'm under pressure, dont have time to do it properly.

byeeeee

 

[oraclesmith]

In Oracle SQL you can dynamically create a 'table', which only exists for the duration of the query. It's tough on resources but it might work in other SQL's too.

eg.

Update table2 t2 set nolines = (select t3.numlines from
table1 t1, (select id, count(*) numlines from table1 group by id) t3
where t1.id = t2.id
and t1.id = t3.id
and t1.somecode = 2)


Usual caveats apply.

 

[lilelvis2000]

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.

Sql server update statment syntax is a bit goofy: I think it should be like

update table t2 set nolines = t3.numlines from (select id, count(*) numlines from table t1 group by id) t3 where t2.id=t3.id

which might work...I haven't done T-SQL in a loooong time.