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.

For example:

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.

you have this in here
INNER
JOIN stop
ON stop.stopcompletiondatetime = jobhistory.modifieddt

which will not work, because stopcompletiondatetime does not equal your modifieddt

Thanks for the reply. What will work for the query?

i can't completely tell the structure, but i believe this will work, just make sure you do a backup first!!!

UPDATE jobhistory 
SET modifieddt = 
(SELECT stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10)

if it complains about multiple values do a select top 1 from the subquery

Thanks for the reply.

I the query you created, but I received: Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I then changed the query to:

UPDATE jobhistory 
SET modifieddt = 
(SELECT top 1 stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10)

but now I receive the message:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ModifiedDT', table 'JobHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Any ideas?

sorry long day already, i didn't have the join on the original table, lets go back to your query the first time with that one line removed

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.stopid = jobxstop.stopid
WHERE jobhistory.JobStatusID = 10

does this one complain about the null values?

Yes, I get Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ModifiedDT', table 'JobHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

hmmmm,

run this

select jobhistory.histjobid, stop.stopcompletiondatetime
from jobhistory
inner join jobxstop 
on jobxstop.jobid = jobhistory.histjobid
inner join stop
on stop.stopid = jobxstop.stopid
where jobhistory.JobStatusID = 10
and jobxstop.isdrop = 1

think there might be something funny going on here

Thanks for your help...that query returns the date/time values that I want for the ModifiedDt.

How would the update script work then?

but all stop.stopcompletiondatetime are not null?

correct, all stop.stopcompletiondatetime rows returned are not null

so close, just something in there i'm just not catching right now, one last try for the evening, cross our fingers

UPDATE jobhistory 
SET modifieddt = 
(SELECT top 1 stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10
and jobhistory.histjobid = jh.histjobid)
where jobhistory.jobstatusid = 10

Thanks for the help, unfortunately when I tried to execute it, I received: Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ModifiedDT', table 'JobHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Please don't stay up late to help with this, if you have a chance tomorrow to look at it, that'd be great.

its not late, only 5, just got a few left before work is done though, there is some data in here that isn't jiving

this will do a partial update, but might help determine what is going on

add this bottom line here and then we can see what rows aren't updated

and jh.jobstatusid = 10
and jobhistory.histjobid = jh.histjobid
and stop.stopcompletiondatetime is not null)

I added the row so the query is now:

UPDATE jobhistory 
SET modifieddt = 
(SELECT top 1 stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10
and jobhistory.histjobid = jh.histjobid
and stop.stopcompletiondatetime is not null)
where jobhistory.jobstatusid = 10

but I still received:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ModifiedDT', table 'JobHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

If I put that line at the end:

UPDATE jobhistory 
SET modifieddt = 
(SELECT top 1 stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10
and jobhistory.histjobid = jh.histjobid)
where jobhistory.jobstatusid = 10
and stop.stopcompletiondatetime is not null

I receive Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'stop' does not match with a table name or alias name used in the query.

Did I put the row in the wrong place?

the stop table is only used in the subquery and can't be referenced outside of it

i don't get why, but it looks like the isdrop =1 is not just joining on those records

try adding this piece in as well

inner join
jobxstop jxs
on jxs.isdrop = 1 and jxs.stopid = stop.stopid

ok, this is now what I have for the query:

UPDATE jobhistory 
SET modifieddt = 
(SELECT top 1 stop.stopcompletiondatetime 
from stop
inner join
jobxstop jxs
on jxs.stopid = stop.stopid
AND jxs.isdrop = 1 and jxs.stopid = stop.stopid
inner join jobhistory jh
on jh.histjobid = jxs.jobid
where jxs.isdrop = 1
and jh.jobstatusid = 10
and jobhistory.histjobid = jh.histjobid
and stop.stopcompletiondatetime is not null)
where jobhistory.jobstatusid = 10

but I still receive Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ModifiedDT', table 'JobHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Did I not update the query correctly? If not, please note with the whole query (to eliminate my errors), thanks again for your help

Thank you for the help, I was able to get the script to work.

Awesome, you happen to know what the issue was with the one we were working with?

This is the script that I ended up using:

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.stopid = jobxstop.stopid
WHERE jobhistory.JobStatusID = 10
AND stop.stopcompletiondatetime IS NOT NULL

Thanks again for all of your help.