Update column of one table with aggregate value from other table

[CarrahaG]

Hi

I would like to update the column of one table with an aggregate value (avg) from another table but I keep getting an error.

I run the following statements:

CREATE TABLE [dbo].[IM_ITEM]
(
[ITEM_NO] VARCHAR(20),
[AVGFACTOR] DECIMAL(15,4)
)

CREATE TABLE [dbo].[PO_RECVR_HIST_LIN]
(
[ITEM_NO] VARCHAR(20),
[COST] DECIMAL(15,4),
[RECVD_COST] DECIMAL(15,4)
)

UPDATE A
SET [AVGFACTOR] = B.[AVGFACTOR]
FROM [dbo].[IM_ITEM] A INNER JOIN

(SELECT avg(COST/RECVD_COST) AS [AVGFACTOR]
FROM [dbo].[PO_RECVR_HIST_LIN]
GROUP BY [ITEM_NO]) B

ON A.[ITEM_NO] = B.[ITEM_NO]

However, I keep getting the error below:


Error in statement # 1

Invalid column name 'ITEM_NO'
Invalid column name 'ITEM_NO'


What am I doing wrong?

Regards,
Georges

 

[markros]

You forgot to add Item_No field into the derived table.

UPDATE ASET [AVGFACTOR] = B.[AVGFACTOR]
FROM [dbo].[IM_ITEM] A INNER JOIN 
(SELECT [b]ITEM_NO[/b], avg(COST/RECVD_COST) AS [AVGFACTOR] 
FROM [dbo].[PO_RECVR_HIST_LIN] GROUP BY [ITEM_NO]) B
ON A.[ITEM_NO] = B.[ITEM_NO]

PluralSight Learning Library

 

[markros]

UPDATE ASET [AVGFACTOR] = B.[AVGFACTOR]
FROM [dbo].[IM_ITEM] A INNER JOIN (SELECT [COLOR=red yellow]ITEM_NO,[/color] avg(COST/RECVD_COST) AS [AVGFACTOR] FROM [dbo].[PO_RECVR_HIST_LIN] GROUP BY [ITEM_NO]) B
ON A.[ITEM_NO] = B.[ITEM_NO]

PluralSight Learning Library

 

[CarrahaG]

Hello Markros

Your advice worked. Thank you.

Regards