Forum Discussion
vaclavnasel
Aug 27, 2024Copper Contributor
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
- rodgerkongIron Contributor
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
- vaclavnaselCopper ContributorThank you rodgerkong, this is the solution what I was looking for 🙂
- vaclavnaselCopper Contributor
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
- rodgerkongIron Contributor
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?