SET [MIBOMD].[partId] = [assy].[partId]
FROM [MIBOMD] INNER JOIN [assy] ON [MIBOMD].[partId] = [assy].[partId]
WHERE bomRev='A' and [bomItem]='600797' AND [MIBOMD].[partId]!=[assy].[partId];
UPDATE m SET [partId] = a.[partId] FROM [MIBOMD] m INNER JOIN [assy] a ON m.[bomItem] = a.[ItemId] AND m.bomEntry = a.bomEntry WHERE m.bomRev='A' AND m.[bomItem]='600797' AND m.[partId]!=a.[partId];
You actually were pretty close! Just a couple of key differences. Before I explain I have used Table Aliases in the code I provided it is a shorthand way of referring to the table throughout the query that will make it a little easier to follow and read. To Alias a table after the table name in the from statement simply add a space and an alias or a space " AS " alias.
Now your Join as on
partid in your version and that was your main issue. Because you want the records where
partid are not the same so you can change the
partid of the
assy table. Looking at your dataset I was able to determine that the shared key was
assy.ItemId. After clearing that up everything should be good.
Per your comment the only other thing that needed to be added was a second condition on the join to make it unique.
[MIBOMD].bomEntry = assy.bomEntry
A little about join conditions. Typically you always want to figure out what the unique relationship between the 2 tables are (
bomEntry = bomEntry) and that is what will go in the ON area of the join. Rarely that will be different and will be for very specific purposes.