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.

Edited by donjt81: Formatting

6 Years
Discussion Span
Last Post by donjt81

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

Edited by adam_k: tags

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.