Microsoft Tech Community is in Read Only mode.  Please enjoy browsing our content while we complete our platform upgrade.

Forum Discussion

vaclavnasel's avatar
vaclavnasel
Copper Contributor
Aug 27, 2024

Upgrade values in table

Hello ALL,
I need to retrieve values a cost, a rate to a table, which are stock in another table.
It is table fs_sro_labor_mst and
Those tables have a mutual column. It is work_code.

 

Total count the work codes is 2 934.

How can I figure out this issue?

Thanks for all solutions

 

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    vaclavnasel 

    If work_code is unique in the source table (fs_sro_labor_mst) and is also unique in the target table (you don't give a name), you can use SQL below, replace table1 with your target table name

    UPDATE T1
    SET
      T1.cost = T2.cost,
      T1.rate = T2.rate
    FROM
      table1 T1 INNER JOIN fs_sro_labor_mst T2 ON T1.work_code = T2.work_code --Replace table1 with your target table name
    • vaclavnasel's avatar
      vaclavnasel
      Copper Contributor
      Thank you rodgerkong, this is the solution what I was looking for 🙂
    • vaclavnasel's avatar
      vaclavnasel
      Copper Contributor

      rodgerkong 

      I try to execute the script on sample of data. Unfortunately, this script doesn't work in the production database. Could be any problem with column work_code? It is foreign key in the table fs_sro_labor_mst

       

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor

        vaclavnasel 

        It looks like table fs_sro_labor_mst is using trigger for data validation, and LEFT JOIN might get NULL values from fs_work_code_mst.cost and fs_work_code_mst.rate. These maybe the causing of the error. Why not try with INNER JOIN?

Resources