The multi-part identifier "[alias].[field]" could not be bound

[SyntaxTerror]

SQL Server 2005 is saying that Wk1Usage.Wk1Usage in the update statement for my temp table can't be bound, but I don't see any problems with my syntax. Help? :S The subquery in the update runs flawlessly.

CREATE TABLE #temp_usage
(
	SiteID		CHAR(10),
	InvtID		CHAR(30),
	Wk1Usage	FLOAT,
	Wk2Usage	FLOAT,
	Wk3Usage	FLOAT,
	Wk4Usage	FLOAT,
	Wk5Usage	FLOAT,
	Wk6Usage	FLOAT,
	Total		FLOAT
)

INSERT INTO #temp_usage
SELECT	SiteID,
		InvtID,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL
FROM	dbo.itemsite
WHERE	invtid NOT LIKE '%NS%'
		AND invtid NOT LIKE '%MER%'
GROUP BY SiteID, InvtID

UPDATE #temp_usage
SET Wk1Usage.Wk1Usage=usage.Wk1Usage
FROM #temp_usage as usage
	LEFT JOIN (	SELECT counts.SiteID as SiteID, 
						xtran.invtID as invtID, 
						(xtran.PreviousCount+pos.Wk1POqty-xtran.currentcount) AS Wk1Usage
				FROM xinvtcounttran as xtran
					LEFT JOIN #temp_counts AS counts ON counts.Week1CountID=xtran.CountID
					INNER JOIN #temp_pos AS pos ON pos.InventoryID=xtran.invtID
				WHERE counts.SiteID='A01-01' AND pos.SiteID='A01-01') as Wk1Usage
	ON usage.SiteID=Wk1Usage.SiteID AND usage.InvtID=Wk1Usage.InvtID
WHERE usage.SiteID=Wk1Usage.SiteID AND usage.InvtID=Wk1Usage.InvtID

 

[gmmastros]

Your problem is here:

UPDATE #temp_usage
SET [!]#temp_usage[/!].Wk1Usage=usage.Wk1Usage
FROM #temp_usage as usage

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Thank you! Worked like a charm. That has to do with order of evaluation, yeah?

I thought I'd be able to use the alias for the SET if I declared it in the FROM, much like how you can use a table alias that you declare in the FROM where you join to refer to fields in the select.

...if that makes sense.

 

[gmmastros]

Actually... the problem is that you can only update data in one table at a time. The rule is... what ever follows the UPDATE is the thing that is getting updated. In your case, it's the #temp_usage table that follows the UPDATE. So, only data in the #temp_usage table can be updated, therefore, that is the only valid table name to the left of the equal in the SET clause.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SyntaxTerror]

Sure does I'll sort of mentally break off UPDATE and SET from the rest of the pack; FROM and everything else just determines what I'm setting and updating.

Thanks again!