user6305775 user6305775 -5 years ago 122
SQL Question

How to update table field value in one table from field value in another table

I am trying to update field value from one table to another.

Item with bomRev='A' in Destination table look like show below
enter image description here

Same Item bomRev='A' in source table looks like

enter image description here

I want to update

field in destination table for
by the value in Source filed i want to destination looks exactly like the source.

I tried this but no luck

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];

Answer Source
    SET [partId] = a.[partId] 
    [MIBOMD] m
    [assy] a
    ON m.[bomItem] = a.[ItemId]
AND m.bomEntry = a.bomEntry
    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 mibomd.bomItem and 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 (bomItem = ItemId and 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download