Your syntax will join every row in A with every row in B - and only the last one will be retained, hence it looks like you are getting only a single value.
I suspect what you are after is:
Set A.Value = B.Value
From TABLE_A AS A
JOIN TABLE_B AS B
ON B.PartNumber= A.PartNumber
Where B.PartNumber in (Select Distinct(PartNumber) from A)