Forum Discussion
Upgrade values in table
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
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
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?
I'm not sure if it was alias reason, try this again
UPDATE sl
SET sl.cost = wc.cost
,sl.rate = wc.rate
FROM fs_sro_labor_mst sl
INNER JOIN fs_work_code_mst wc ON sl.work_code = wc.work_code
WHERE sl.[type]='P'If it is not working, try execute this and check if its result more than 0
SELECT COUNT(*) FROM
fs_sro_labor_mst sl
INNER JOIN
fs_work_code_mst wc
ON sl.work_code = wc.work_code
WHERE sl.[type]='P'
and ((wc.cost IS NULL) OR (wc.rate IS NULL))If it still does not work, I have to check the trigger script...