how to update more than two columns of one table depending upon different value of another table??

Hemant Kumar

14y3.4kSQL

suppose i have one table ShiftMaster in this i have columns shiftname and shift code is there, another table i have shiftroster where sunday monday.... are columns for each day i am selecting shiftname n saving its corresponding shiftcode in database now i am updating shiftcode in shiftmaster and i want that changes affect in my shiftroster also for that day only shiftcode should be changed where that shiftname is assigned

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

tejal bhesaniya14y ago
HEY Sivaraman M also not telling him to change the code in master table,,,he need to update the code in roster if he is updating a code he need to pass proper paramter for where comdition too..
0
Sivaraman Dhamodaran14y ago
If you update the shiftcode in the Master, you will lose the older code in the master. So your shiftroster (With all older table rows) exists with junk value. I mean you can not do a mapping for old values in roster with master. Is that OK. If you say yes, you can go with tejal bhesaniya
0
tejal bhesaniya14y ago
Hi, Hemant You can create one trigger AFTER INSERT or UPDATE in that u can write one Merge statement and pass Your shift Code as a parameter so with the merge statement first it will check the value in the updated table with the same code and will updated or insert the same in shiftroster create trigger trg_shift_master on shiftmaster after update as BEGIN MERGE Shiftroster AS sht USING (SELECT ShiftCode FROM shiftmaster) AS sd ON sht.shiftcode = sd.shiftcode WHEN MATCHED THEN UPDATE SET sht.shiftcode = sd.shiftcode WHEN NOT MATCHED THEN INSERT(---------) VALUES(--------); END If its ok then mark it as a answer
0