>Dear all,
>
>I encountered a update syntax problem while I was developing a stored procedure on the SQL server. The code is following,
>
>code:
>create table #t1 (year smallint,person char(10),tgv int)
>...
>...
>...
>update mtopdev set pv=#t1.tgv ,bonus= (@wholesale*(0.01)*(#t1.tgv))/(@totalgv)
>where year=@iyear and month=12 and btype='2' and person in ( select person from #t1)
>--End of the code---
>
>Where #t1 is an temporary table for keeping the data produced on the way, and the goal is to update the data in table mtopdev according to those in #t1. Error reported was " the column prefix #t1 does not match with a table name or aliaas name used in the query. "
>Please someone tell me what's wrong with it. Many thanks.
>
>Best regards,
>Ron.

SQL Server is not like VFP. You cannot open a table and position a record ptr and then refer to the table. Depending on what your trying to do, you may have to expand on your UPDATE statement. For instance say that we want to increase the rate base on a value in another table. We could write an UPDATE command list this (ANSI)

UDPATE t1
SET rate = rate + (SELECT increase FROM t2 WHERE id = t1.id)

Be warned that using a subquery as an expression has the requirement that the subquery return one row with one value.

I can't really tell what you're trying to do. If #t1 is a temp. collection table that you want to iterate through, you could use a CURSOR but that will be slow.

If you post the entire stored procedure I or someone else might have a better chance of helping you through the problem.

-Mike