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?