Table variable and ambiguous column

[icrf]

I'm having issues resolving the ambiguity of column names with a table variable. A short example:

declare @Inventory table(
	ProductID int,
	Quantity int
)

UPDATE @Inventory SET ProductID = NewProductID FROM tblNewProducts 
WHERE tblNewProducts.ProductID = @Inventory.ProductID

It gives me the error

Must declare the variable '@Inventory'.

If I comment out the WHERE clause, it parses fine. Dropping the table variable prefix bring the expects ambiguous column error.

Is there some other syntax for this?

 

[vongrunt]

What about this:

UPDATE I
SET ProductID = NewProductID 
FROM @Inventory I
INNER JOIN tblNewProducts
ON tblNewProducts.ProductID = I.ProductID

 

[icrf]

Looks good, works better. I kept trying to alias the table on its first reference (between UPDATE and SET) with results as poor as you'd expect.

Thanks for the quick reply.

________________________________________
Andrew

I work for a gift card company!