Upgrade values in table

Transact-SQL
1

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.

image

The work_code table

work_code cost rate
EY9 82.3 102.9
EZA 123.4 154.3
EZB 102.9 128.6
P1 4.8 6.1
P2 9.7 12.1
P3 14.5 18.2
WD 96.8 121.0

Total count the work codes is 2 934.

How can I figure out this issue?

Thanks for all solutions

2

please show the necessary working to obtain the value 2 934

3

I am not sure I understand what you're asking for, but I'll take a guess.

SELECT fslm.*, wc.cost AS wc_cost, wc.rate AS wc_rate
FROM fs_sro_labor_mst fslm
LEFT OUTER JOIN
     work_code wc
  ON fslm.work_code = wc.work_code
  ;
4

It is amount of work codes only

image
image608×739 25.5 KB

5

No I didn´t explain my issue right. I need the same values for rate and cost in the table fs_sro_labor_mst according the table of work_code. The source of values is in the table work_code.
Is it clear now?

6
1 Like
7

Are you saying you need to UPDATE the table fs_sro_labor_mst and replace the cost and rate values with the values from the fs_work_code_mst table where they match on the work_code column?

UPDATE sl
SET cost = wc.cost
  , rate = wc.rate
FROM fs_sro_labor_mst sl
INNER JOIN
     fs_work_code_mst wc
  ON sl.work_code = wc.work_code
  ;
8

Thank you SqlHippo, this is the solution what I was looking for :grinning:

9

The solution runs on sample of data which I create from a csv. But there is problem, because the column work_code in fs_sro_labor_mst is
image
Do you undrestand?

10

This is an error what I see:

image
image1007×606 97.4 KB

11

There is at least 1 trigger in place that has some logic in it that prevents this update. The trigger is likely keeping you from accidentally breaking something. You'll have to examine the triggers in place on the fs_sro_labor_mst table to figure out what they're doing.

12

The trigger checks if does sro_num exist in an one table.

I did the trigger on disabled during update and your script was running. Then I did the trigger on enabled.

Is it any solution in your opinion?

13

There's no way for me to answer that with certainty. I hope you haven't added invalid data to the database by circumventing the trigger.

14

Thank you very much for all.

Good luck SqlHippo