error using temporary table

[junkmail]

Can someone tell me why I get the following error with the code provided?

Msg 4104, Level 16, State 1, Procedure sp_Test, Line 20
The multi-part identifier "#temp.id" could not be bound.

select * into #temp from billtrack where id in(select min(id) from billtrack
group by customer )
update billtrack
set test = 1
where #temp.id = billtrack.id

 

[bborissov]

You should JOIN the table:

select * into #temp from billtrack where id in(select min(id) from billtrack
group by customer )

update billtrack set test = 1
FROM billtrack
INNER JOIN #temp.id = billtrack.id

Borislav Borissov
VFP9 SP2, SQL Server

 

[imex]

Try:

with CTE_RN as
(
    select 
        *,
        ROW_NUMBER() OVER(PARTITION BY customer ORDER BY id) as RN
    from billtrack
)

update CTE_RN
set test = 1
where RN = 1

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]