UPDATE column only when tricky criteria has been meet.
-
Hi all,
sorry about the vague Subject heading, but I am having a nightmare trying to work this one out. I have two tables Inspections and Answers where basically one Inspection can have many Answers (They are linked via the InspectionUID column).
What I want to do is to set the value of the Inspections.ToSynch column to '0' if and only if the following is true:
* The Inspection.SignedOffDate has a value other than NULL (this means the Inspection is signed off, AND the Answers.HasActions column in the Answers table is '0' for all Answers related to each Inspection. (This means an Inspection was undertaken, no Actions were raised, and the Inspection is signed off - we dont need to synch this down to the device)
OR
* The Inspection.SignedOffDate has a value other than NULL (this means the Inspection is signed off, AND the Answers.HasActions column in the Answers table is '1' for one or more Answers related to each Inspection BUT the Answers.SignedOffDate IS NOT NULL (has a date value). (This means an Inspection was undertaken, Actions were raised and they have been signed off, and the Inspection is also signed off - - we dont need to synch this down to the device)
What I did have is the following, which doesn't seem to work...
UPDATE tbl_NSP_Inspection
SET ToSynch = 0
WHERE (InspectionUID IN
(
SELECT INSP.InspectionUID
FROM tbl_NSP_Inspection AS INSP
INNER JOIN tbl_NSP_Answer AS ANS ON (INSP.InspectionUID = ANS.InspectionUID)
WHERE ((INSP.SignedOffDate IS NOT NULL)
AND (ANS.HasActions = 1)
AND (ANS.SignedOffDate IS NOT NULL))
OR ((INSP.SignedOffDate IS NOT NULL) AND (ANS.HasActions = 0))
GROUP BY INSP.InspectionUID
))
I would be very greatful if you could help me out.
Thanks in advance.
Tryst
-
This should work for you but you will definately need to run this through and look at the execution plan.
UPDATE Ins
SET ToSynch = 0
FROM tbl_NSP_Inspection Ins
INNER JOIN (
SELECT DISTINCT Ins.InspectionUID
FROM tbl_NSP_Inspection Ins
INNER JOIN (
SELECT Ins.InspectionUID, SUM(HasActions) as ActionsSUM
FROM tbl_NSP_Inspection Ins
INNER JOIN tbl_NSP_Answer Ans
ON Ins.InspectionUID = Ans.InspectionUID
WHERE Ans.SignedOffDate IS NULL
GROUP BY Ins.InspectionUID
) t
ON Ins.InspectionUID = t.InspectionUID
INNER JOIN tbl_NSP_Answer Ans
ON Ans.InspectionUID = Ins.InspectionUID
WHERE Ins.SignedOffDate IS NOT NULL AND t.ActionsSUM = 0
) t
ON Ins.InspectionUID = t.InspectionUID
-
That seems to have done it! Thanks very much.
I am going to be checky here
Is there anyway you can just give a brief desc of the whats happening. Is each SELECT that is INNER JOIN'd working on the criteria I specified in my initial post?Thanks
Tryst
-
I think the best way to grasp what this is doing would be to set up some mock data and break the query down into parts to see what it is doing. From a high level, I've created a derived table that sums up the HasActions values. The result of the update will ultimately depend on the sum of the HasActions column. So to relate this to your requirements, if all of the HasActions values are zero, the sum will be zero and the ToSynch will be updated. You'll notice that the derived table is getting the sum where the tbl_NSP_Answer.SignedOffDate IS NULL. This will ignore rows where the HasActions is 1 and a date exists. This means that the sum for each InspectionUID will be zero if there were no actions or there were actions and they've been signed off on. If any of the actions have not been signed off on, the sum for that InspectionUID will be > 0. That derived table is then joined back to the other two tables so that we can make sure the tbl_NSP_Inspection.SignedOffDate exists.
Clear as mud??
In looking back through my previous post, I see that there is an additional join that is not needed, here is a revised version:
UPDATE Ins
SET ToSynch = 0
FROM tbl_NSP_Inspection Ins
INNER JOIN (
SELECT DISTINCT Ins.InspectionUID
FROM tbl_NSP_Inspection Ins
INNER JOIN (
SELECT Ins.InspectionUID, SUM(HasActions) as ActionsSUM
FROM tbl_NSP_Inspection Ins
INNER JOIN tbl_NSP_Answer Ans
ON Ins.InspectionUID = Ans.InspectionUID
WHERE Ans.SignedOffDate IS NULL
GROUP BY Ins.InspectionUID
) t
ON Ins.InspectionUID = t.InspectionUID
WHERE Ins.SignedOffDate IS NOT NULL AND t.ActionsSUM = 0
) t
ON Ins.InspectionUID = t.InspectionUID
-
Thanks for the low down, John. Very helpful.
I guess I was trying to do too much in a WHERE clause, but breaking down each requirement into its own sub-query seems a more efficient way of doing things.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply