Need help with SQL update query

[chad_jensen71]

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.

[dickersonka]

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

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

[chad_jensen71]

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

[dickersonka]

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

[chad_jensen71]

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?

[dickersonka]

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?

[chad_jensen71]

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.

[dickersonka]

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

[chad_jensen71]

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

How would the update script work then?

[dickersonka]

but all stop.stopcompletiondatetime are not null?

[chad_jensen71]

correct, all stop.stopcompletiondatetime rows returned are not null

[dickersonka]

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

[chad_jensen71]

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.

[dickersonka]

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)

[chad_jensen71]

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?

[dickersonka]

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

[chad_jensen71]

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

[chad_jensen71]

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

[dickersonka]

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

[chad_jensen71]

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.