CODEWITHSUNDEEP
sqlsql-servert-sql
Jeff McIlvain
Jeff McIlvain

Reputation: 3

Update from another table

I have a table that I need to update with data from another table. Problem is there is no foreign key relationship between the two tables. However, there is a third table that has the relationship.

Here are the tables:

Parent table:

ParentKey     ParentField
-------------------
 p1           aaa
 p2           bbb
 p3           ccc

Child table:

ChildKey     ChildField
-------------------
 c1           ccc
 c2        
 c3

Relationship table:

ParentKey    ChildKey
-------------------
 p1          c2
 p2          c3
 p3          c1

Here's what I want to do... If the Child table does not have a value in ChildField then I want to update the ChildField with the value of the corresponding ParentField. So basically my final result should look like this:

Child table:

ChildKey     ChildField
-------------------
 c1           ccc
 c2           aaa
 c3           bbb

Upvotes: 0

Views: 126

Answers (2)

apartridge
apartridge

Reputation: 1820

This should work for you;

UPDATE ChildTable ct SET ct.ChildField = 
    (SELECT MAX(ParentField) FROM ParentTable pt 
    INNER JOIN RelationshipTable rt ON rt.ParentKey=pt.ParentKey 
    WHERE rt.ChildKey=ct.ChildKey)
WHERE ct.ChildField IS NULL

If you have an empty ChieldField as an empty string rather than NULL, try

UPDATE ChildTable ct SET ct.ChildField = 
    ISNULL((SELECT MAX(ParentField) FROM ParentTable pt 
    INNER JOIN RelationshipTable rt ON rt.ParentKey=pt.ParentKey 
    WHERE rt.ChildKey=ct.ChildKey),'')
WHERE ct.ChildField=''

Upvotes: 0

Greg
Greg

Reputation: 3522

Even without a foreign key, you can still join the two tables together to do the update:

update      child
set         childfield = parent.parentfield
from        child
inner join  Relationship on Relationship.ChildKey = Child.ChildKey
INNER JOIN  Parent on PArent.ParentKey = Relationship.ParentKey
WHERE       Child.ChildField IS NULL

This should work in Microsoft SQL Server. Pretty sure it will work elsewhere as well

Upvotes: 2

Related Questions