Updating values between tables

[donjt81]

I need some help updating data in my table based on data in the supply chain group's table

This is how the table looks on their side

Table Name: ProductSC

ID   Code      Description  Status
1    N461579   Phone        Available
2    N836257   Headset1     Discontinued
3    N836258   Headset2     Available
4    N748956   Adapter      Available

On my side, instead of just entering the text for Status, I have it normalized into another table.

Table name: ProductMine

ID   Code      Description  StatusID
1    N461579   Phone        1
2    N836257   Headset1     2
3    N836258   Headset2     1
4    N748956   Adapter      1

Table name: Status

StatusID     Status
1            Available
2            Discontinued

I need to run a sync from their table to mine, every night. So for example if today they change the status of "Adapter" to Discontinued, my sync needs to update that status on my side as well, over night. Their table is on a different server and different database. but I can manage that using Linked servers. For the sake of simplicity, we can assume all tables above are in the same database.

[adam_k]

Give this a try

     update productmine 
     set a.statusid = b.statusid
     from productmine a inner join productSC sc 
     on a.code = sc.coe
     inner join status b on sc.status = b.status

[donjt81]

Thank you. That worked.