Update without temp table 1

[micang]

Hi All,

SQL 2008

I have 2 tables, I would like to update the second table with results from the first table. Currently I am doing it like, using a temporary table:

select column1 ,COUNT(*) as vol
into #temp1
from table1
group by column1

UPDATE    table2
SET              column = vol
FROM         table2 INNER JOIN
                      #temp1 ON table2.column2 = #temp1.column1

drop table #temp1

I was wondering if it is possible doing this, but without the need of a temporaty table ?

Any information is appreciated.

Thanks

Michael

 

[bborissov]

Sure,

select column1 ,COUNT(*) as vol
into #temp1
from table1
group by column1

UPDATE table2
       SET column = vol
FROM table2
INNER JOIN (select column1 ,COUNT(*) as vol
                   from table1
            group by column1) Tbl1
      ON table2.column2 = Tbl1.column1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

OOOOPS,
Copy and paste. Use just this:

UPDATE table2
       SET column = vol
FROM table2
INNER JOIN (select column1 ,COUNT(*) as vol
                   from table1
            group by column1) Tbl1
      ON table2.column2 = Tbl1.column1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[micang]

Thanks so much bborissov, appreciate it.

Michael