Update query with INNER JOIN 1

[rwn]

This is the UPDATE Query Im trying to execute on the SQL Server, but it errors out on the INNER Join.

UPDATE Material_Location INNER JOIN Material ON Material_Location.Material = Material.Material SET Material_Location.On_Hand_Qty = 0
WHERE (((Material.Class)='Assy'));

 

[djj55]

You need to rearrange the code and add from

UPDATE M
SET Material_Location.On_Hand_Qty = 0
FROM Material_Location INNER JOIN Material ON Material_Location.Material = Material.Material  
WHERE (((Material.Class)='Assy'));

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Sorry forgot the alias for Material_Location M

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[rwn]

error results.

Msg 208, Level 16, State 1, Line 1
Invalid object name 'M'.

 

[rwn]

correct by:

UPDATE Material_Location
SET Material_Location.On_Hand_Qty = 0
FROM Material_Location INNER JOIN Material ON Material_Location.Material = Material.Material
WHERE (((Material.Class)='KFLEX'));