Update Query Not Updating All Required Records

[LeonelSanchezJr]

When I run the query below, I get 295 rows back. However, when I run my Update query I only get 158 rows updated. What am I doing wrong?

select hlpdsk_all.cproblemnum, hlpdsk_all.ccallnot
es, callnotes.ccallnotes childrecord
from hlpdsk_all
left join helpdesk on hlpdsk_all.cproblemnum = helpdesk.cproblemnum
left join callnotes on helpdesk.icallnoteskey = callnotes.id
where hlpdsk_all.drecorded >= '05/23/01' and hlpdsk_all.drecorded < '05/24/01'


UPDATE QUERY BELOW
UPDATE Callnotes SET cCallNotes = c.cCallNotes
FROM Callnotes a
INNER JOIN HelpDesk b
ON a.ID=b.icallnoteskey
and b.drecorded >= '05/23/01' and b.drecorded
<'05/24/01'
INNER JOIN Hlpdsk_All c
ON b.cproblemnum=c.cproblemnum
WHERE c.drecorded >= '05/23/01' and c.drecorded
<'05/24/01'

 

[tlbroadbent]

You've defined different criteria in the two queries.

UPDATE QUERY BELOW
UPDATE Callnotes SET cCallNotes = c.cCallNotes
FROM Callnotes a
INNER JOIN HelpDesk b
ON a.ID=b.icallnoteskey
and b.drecorded >= '05/23/01' and b.drecorded <'05/24/01'
INNER JOIN Hlpdsk_All c
ON b.cproblemnum=c.cproblemnum
WHERE c.drecorded >= '05/23/01' and c.drecorded
<'05/24/01'

and b.drecorded >= '05/23/01' and b.drecorded <'05/24/01' doesn't exist in the Select Query, only in the Update Query.

I view the inclusion of the date criteria in the ON clause as an error in the query. This kind of error doesn't always result in erroneous results but often does. The ON clause should be used to define the Join relationship. I think it is unfortunate that SQL Server doesn't flag this as a syntax error.

You can fix this in one of two ways.

First suggestion: Convert the date criteria to a Join relationship.

UPDATE QUERY BELOW
UPDATE Callnotes SET cCallNotes = c.cCallNotes
FROM Callnotes a
INNER JOIN HelpDesk b
ON a.ID=b.icallnoteskey
and b.drecorded=c.drecorded
INNER JOIN Hlpdsk_All c
ON b.cproblemnum=c.cproblemnum
WHERE c.drecorded >= '05/23/01' and c.drecorded
<'05/24/01'

Second suggestion: Move the date test to the Where clause.

UPDATE QUERY BELOW
UPDATE Callnotes SET cCallNotes = c.cCallNotes
FROM Callnotes a
INNER JOIN HelpDesk b
ON a.ID=b.icallnoteskey
and b.drecorded=c.drecorded
INNER JOIN Hlpdsk_All c
ON b.cproblemnum=c.cproblemnum
WHERE c.drecorded >= '05/23/01'
AND c.drecorded <'05/24/01'
AND b.drecorded >= '05/23/01'
AND b.drecorded <'05/24/01'

You'll need to apply whichever fix you choose to the select query also if you want to get the same count. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[LeonelSanchezJr]

Thank you for your help, however, neither one of the two suggestions yields the results I need. Both of your suggestions including my original only update 158 instead of 295.

 

[tlbroadbent]

I just noticed another &quot;SMALL&quot; detail. The select query has LEFT OUTER JOINS while the update query has INNER JOINS. You cannot expect these two queries to return the same row count unless all the records defined on the left have matches on the right side of the JOIN. Your results indicate this is not the case.

Make sure the Select and Update query criteria match and then check the results! Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[tlbroadbent]

The SELECT query, with the LEFT JOIN, returns all rows from hlpdsk_all regardless of the existance of a matching key on the other tables. Your UPDATE query, with INNER JOIN, will only update rows in callnotes that have a matching key on the other tables.

It appears that you want to UPDATE rows that already exist on callnotes and INSERT records from hlpdsk_all that don't exist on callnotes. You'll need to do two queries, the current UPDATE query and an INSERT query based on the SELECT query.

Add this to the criteria in the SELECT query to find the no matches. AND callnotes.callnotes is null

select hlpdsk_all.cproblemnum, hlpdsk_all.ccallnotes, callnotes.ccallnotes childrecord
from hlpdsk_all
left join helpdesk on hlpdsk_all.cproblemnum = helpdesk.cproblemnum
left join callnotes on helpdesk.icallnoteskey = callnotes.id
where hlpdsk_all.drecorded >= '05/23/01' and hlpdsk_all.drecorded < '05/24/01'
AND callnotes.callnotes is null
Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.