I am trying to write a SQL update query for a database column containing an incorrect value.
In the JobHistory table (whose primary key = JobID), the ModifiedDt column contains an invalid value, specifically it contains a value from a pickup stop, but I need to use the value for the drop stop.
The Stop table (whose primary key = StopID) contains the StopCompletionDateTime value for both the pickup stop and drop stop.
The JobXStop table (which contains both the JobID and StopID) contains a IsDrop column which defines the stop as a pickup (IsDrop = 0) or drop (IsDrop = 1).
I only want to update rows in the JobHistory table where the JobStatus column = 10.
StopID 10001 (pickup stop) - StopCompletionDateTime = 2009-01-01 12:00:00 StopID 10002 (drop stop) - StopCompletionDateTime = 2009-01-02 15:00:00 JobXStop contains 2 rows for JobID = 1000: StopID 10001 (IsDrop = 0) StopID 10002 (IsDrop = 1)
In the JobHistory DB table, the ModifiedDT for JobID 1000, JobStatusID 10 = 2009-01-01 12:00:00, modifiedDT cannot be null
Could someone please tell me how to update the ModifiedDt of the row containing JobID 1000 (JobStatusID = 10) to 2009-01-02 15:00:00?
I tried the following query:
UPDATE jobhistory SET modifieddt = stop.stopcompletiondatetime FROM jobhistory INNER JOIN jobxstop ON jobxstop.jobid = jobhistory.histjobid AND jobxstop.isdrop = '1' INNER JOIN stop ON stop.stopcompletiondatetime = jobhistory.modifieddt AND stop.stopid = jobxstop.stopid WHERE jobhistory.JobStatusID = '10'
But it did not update the JobID 1000 row.